Fix "unterminated quoted string" in PostgreSQL
ERROR: unterminated quoted string at or near "'Canada;"
Postgres found an opening single quote and kept reading, looking for the closing one, until the query ran out. Either a closing quote is missing, or an apostrophe inside your text (like in didn't) ended the string early and left a stray quote behind.
Why it happens
- A missing closing quote, often at the end of a line or inside an IN list.
- An apostrophe inside the text, like 'Didn't order'. Depending on what follows, this can also show up as a syntax error.
- Text pasted from a document or chat that mixes straight and curly quotes.
How to fix it
1. Close the quote
Fails
SELECT name, email FROM customers WHERE country = 'Canada;
Works
SELECT name, email FROM customers WHERE country = 'Canada';
The error shows where the open string starts. Look just after that point for the missing quote.
2. Double the apostrophe inside text
Fails
SELECT c.name, 'Didn't order' AS note FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
Works
SELECT c.name, 'Didn''t order' AS note FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
Two single quotes in a row ('') mean one literal apostrophe. Don't use a backslash or double quotes: double quotes are for column names in Postgres.
3. Check every value in an IN list
Fails
SELECT id, amount, status
FROM orders
WHERE status IN ('completed', 'pending)
ORDER BY amount DESC;Works
SELECT id, amount, status
FROM orders
WHERE status IN ('completed', 'pending')
ORDER BY amount DESC;Here the quote after pending is missing, so the string swallows the closing parenthesis and the rest of the query.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.