PostgreSQL says a quoted string was never closed
ERROR: unterminated quoted string at or nearShort answer
PostgreSQL started reading a string at a single quote and never found the closing one. The usual reasons are an apostrophe inside the text that was not doubled, a backslash treated as an escape, or a function or trigger body written in single quotes with more quotes inside. Double the quote, use an E string, or use dollar quoting.
Fix it fast
1Find out which cause you have
Look at the text right after the position the error names, and count the single quotes in that statement. An odd number means one is unmatched.
SHOW standard_conforming_strings; SELECT 'Dianne''s horse';2Match what you see, then jump to its fix
- 1
The failing value contains a name or sentence with an apostrophe, such as O'Brien or it's.An apostrophe inside the text was not doubledFix - 2
The text ends with a backslash, or contains a path or \' and the error depends on where the string was written.A backslash is escaping the quote, or is notFix - 3
The error appears on CREATE FUNCTION or CREATE TRIGGER with a body like AS 'BEGIN ... END;'.A function or trigger body in single quotes contains quotes of its ownFix
- 1
What does the error look like?
ERROR: unterminated quoted string at or near
Cause 1 of 3
An apostrophe inside the text was not doubled
1Check
In a standard string, a single quote ends the string. To include one, write it twice. A double quote is not a substitute.
SELECT 'Dianne's horse';2Fix
Double the apostrophe, or wrap the text in dollar quotes. In application code, pass the value as a bound parameter instead of pasting it into the SQL.
SELECT 'Dianne''s horse';
SELECT $$Dianne's horse$$;3Verify
Both statements return Dianne's horse.
SELECT 'O''Brien';Confirmed by postgresql.org
Cause 2 of 3
A backslash is escaping the quote, or is not
1Check
Since PostgreSQL 9.1 standard_conforming_strings is on by default, so backslashes in a normal string are literal. If the setting is off, or the string is an E string, a backslash escapes the next character, including the quote.
SHOW standard_conforming_strings;2Fix
Do not rely on backslash escapes. For a literal path use an ordinary string with doubled quotes or a dollar-quoted string; if you want escapes, say so with an E prefix.
SELECT 'C:\temp\new';
SELECT E'Dianne\'s horse';
SELECT $$C:\temp\new$$;3Verify
The first and third statements keep the backslashes exactly as typed.
SELECT length($$C:\temp\new$$);Confirmed by postgresql.org
Cause 3 of 3
A function or trigger body in single quotes contains quotes of its own
1Check
A body wrapped in single quotes ends at the first inner single quote, so every quote inside has to be doubled. That is easy to miss in a long body.
SELECT 'RETURN ''done'';';2Fix
Wrap the body in dollar quotes. Inside a dollar-quoted body nothing is escaped, and a different tag can be used for each nesting level.
CREATE FUNCTION greet() RETURNS text AS $function$
BEGIN
RETURN 'it''s fine';
END;
$function$ LANGUAGE plpgsql;3Verify
The function is created and callable.
SELECT greet();Confirmed by postgresql.org
How do I stop it coming back?
- Use bound parameters for values; never paste user text into SQL.
- Write function and trigger bodies with $$ or a named tag from the start.
- Keep standard_conforming_strings on and mark real escapes with E.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix