PostgreSQL: How to Escape Single Quotes (') in SQL Statements

This article explains how to escape single quotes (') in PostgreSQL. When a single quote appears inside a string literal that is also enclosed in single quotes, PostgreSQL will throw a syntax error unless the quote is properly escaped.

Example of a Failing SQL Statement

insert into nobuneko_dictionary (dic_name, dic_name2)
values ('Foundation's Edge', 'ファウンデーションの彼方へ');

Running this SQL statement results in the following error:

ERROR: syntax error at or near "s"
LINE 3: ...ictionary (dic_name,dic_name2) values ('Foundation's Edge','...
        ^
********** ERROR **********
ERROR: syntax error at or near "s"
SQL state: 42601
Character: 112

The error occurs because the string 'Foundation's Edge' contains an unescaped single quote inside a single-quoted literal. To avoid this error, the single quote must be escaped.

Successful Example 1: Escape with Two Single Quotes

insert into nobuneko_dictionary (dic_name, dic_name2)
values ('Foundation''s Edge', 'ファウンデーションの彼方へ');

Here, the single quote is escaped by writing it twice. In other words, 'Foundation's Edge' becomes 'Foundation''s Edge'.

Successful Example 2: Escape String Syntax (E'...')

insert into nobuneko_dictionary (dic_name, dic_name2)
values (E'Foundation\'s Edge', 'ファウンデーションの彼方へ');

By prefixing the string with E, PostgreSQL interprets it as an escape string literal. Inside the string, the single quote is escaped with a backslash (\). Thus, 'Foundation's Edge' becomes E'Foundation\'s Edge'.

Successful but Non‑Standard Example (Warning Shown)

insert into nobuneko_dictionary (dic_name, dic_name2)
values ('Foundation\'s Edge', 'ファウンデーションの彼方へ');

This version succeeds, but PostgreSQL displays a warning because the backslash escape is non‑standard unless the string is prefixed with E:

WARNING: nonstandard use of \' in a string literal
LINE 4: ... nobuneko_dictionary (dic_name,dic_name2) values ('Foundatio...
        ^
HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').
Query succeeded: 1 row affected, execution time 16 ms.

According to the warning, using \' inside a normal string literal is non‑standard. The recommended approaches are either:

escape the quote by writing it twice (''), or
use escape string syntax (E'...\'...')