PostgreSQL: unterminated quoted string at or near

What it means

PostgreSQL reads a statement from left to right. A ' starts a string, and the next ' ends it. Here the statement ran out before a closing quote turned up, so everything from the opening quote to the end is one unfinished string:

ERROR:  unterminated quoted string at or near "'abc"
LINE 1: SELECT 'abc
               ^

The pointer is at the quote that was never closed, which isn’t always where the mistake is. With an apostrophe in a value, the string closes early at the apostrophe and the last quote is the one left open:

ERROR:  unterminated quoted string at or near "'"
LINE 1: SELECT 'O'Brien'
                       ^

If the stray quote leaves an even number of them, you get a syntax error at the word after the apostrophe instead (syntax error at or near "Reilly"). Same cause, different symptom.

The same family:

unterminated quoted identifier for a " that never closes;
unterminated dollar-quoted string for $$ or $tag$ without its partner;
unterminated bit string literal when a backslash “escape” turned '…\'b' into a b'… literal.

Common causes

An apostrophe in data pasted or concatenated into SQL: O’Brien, it’s, a product called “Rock ’n’ Roll” typed with a straight '.
Backslash escapes from MySQL ('It\'s'). In PostgreSQL a backslash is an ordinary character in a normal string, so \' doesn’t escape anything.
A function body split at its semicolons. Migration tools, drivers and SQL runners that split scripts on ; cut CREATE FUNCTION … AS $$ BEGIN …; …; END $$ in the middle, and the first piece ends inside the $$.
A statement copied without its end, or a quote lost when a string was edited.

How to fix it

Pass values as parameters

The real fix for data with quotes in it: don’t build SQL by joining strings. Every driver supports placeholders ($1, ?, %s, :name), and the value is sent separately, so quotes in it can’t break the statement. It also closes the door to SQL injection.

Double the quote in literals

In a string written in SQL, a quote is written as two:

SELECT 'it''s fine';            -- it's fine
SELECT $$O'Brien$$;             -- dollar quoting: no escaping inside
SELECT E'O\'Brien';             -- E'' strings understand backslash escapes

quote_literal() and format('%L', …) do the doubling for you in dynamic SQL.

Don’t split function bodies

Run the file with a tool that understands dollar quoting (psql -f migration.sql does), or tell your migration tool to send that statement whole. If the error’s at or near text starts with $$ and stops where your file has a semicolon, the statement was cut there.

Look for the opening quote

In a long statement, the LINE pointer shows where the unclosed string begins. Count quotes from there; an editor with SQL highlighting makes the runaway string obvious, because everything after it is coloured as a string.

Reproduce it

On PostgreSQL 18.6, with psql -c (which sends the text as it is):

SELECT 'abc
ERROR:  unterminated quoted string at or near "'abc"
LINE 1: SELECT 'abc
               ^

SELECT * FROM t WHERE name = 'O'Reilly'
ERROR:  syntax error at or near "Reilly"
LINE 1: SELECT * FROM t WHERE name = 'O'Reilly'
                                        ^

SELECT "abc
ERROR:  unterminated quoted identifier at or near ""abc"
LINE 1: SELECT "abc
               ^

SELECT 'a\'b'
ERROR:  unterminated bit string literal at or near "b'"
LINE 1: SELECT 'a\'b'
                   ^

SELECT 'O'Brien' gave the "'" version shown above. What a semicolon-splitting tool sends as the first piece of a PL/pgSQL function:

CREATE FUNCTION seo_err_pg.f() RETURNS int LANGUAGE plpgsql AS $$ BEGIN RETURN 1
ERROR:  unterminated dollar-quoted string at or near "$$ BEGIN RETURN 1"
LINE 1: ...ON seo_err_pg.f() RETURNS int LANGUAGE plpgsql AS $$ BEGIN R...
                                                             ^

SELECT 'it''s fine', $$O'Brien$$, E'O\'Brien' returned it's fine, O'Brien and O'Brien. With \set VERBOSITY verbose, psql shows the code: ERROR: 42601: unterminated quoted string at or near "'abc". PostgreSQL 14.24 gives the same messages.

In Inlet

The query editor highlights strings as you type, so a string that runs on is visible before you run it, and ⌘↩ runs only the statement under the cursor. When a statement fails, Inlet shows the error at the position the server reports. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

unterminated quoted string at or near