Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL says a quoted string was never closed

ErrorERROR: unterminated quoted string at or near

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The 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. 3The 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.