Quoting Strings in Postgres

PostgreSQL offers multiple ways of quoting string literals. Most programmers are familiar with the first, but not the other forms.

Let’s look at them:

Single-quoted string constants:

$ psql
# SELECT 'What''s up?';
  ?column?
------------
 What's up?
(1 row)

Single-quoted literals are what you’ll see most often in SQL examples and in Postgres’ own documentation.

If you want a literal quote (') in a single-quoted literal string, doubling the quote does the trick. This can be slightly eye-watering, though:

$ psql
# SELECT '''';
 ?column?
----------
 '
(1 row)

C-style string constants

Postgres also supports C-style string quoting using E'<text>' (or e'<text>'), where you escape characters by prefixing them with \:

$ psql
# SELECT e'How\'s my escaping?\n  Not terrible';
      ?column?
--------------------
 How's my escaping?+
   Not terrible
(1 row)

This is particularly handy when you need to mix in tabs and newlines into your strings, and for tricky Unicode characters, like non-breaking spaces, where writing them literally can confuse readers. Common Unicode characters in the basic multilingual plane (BMP) can be referenced as \uXXXX, and characters outside the BMP (such as Emoji) as \UXXXXXXXX, where X are hex digits:

$ psql
# SELECT e'\U0001F60A\u00A0\U0001F60A';
 ?column?
----------
 😊 😊
(1 row)

Unicode escape string constants

Unicode escapes use slightly simpler escape notation for Unicode characters:

$ psql
# SELECT U&'\+01F60A\00A0\+01F60A';
 ?column?
----------
 😊 😊
(1 row)

With U&'' strings, use \XXXX four-hex-digit escapes for BMP characters, and six-digit \+XXXXXX for characters outside the BMP.

Unicode escapes also support customizing the escape character with the UESCAPE keyword which is occasionally handy if, say, you’re trying to feed psql text via a command-line pipeline, and escaping \ is giving you a migraine.

Dollar-quoted string constants

Dollar-quoted strings are extremely useful when you must put SQL or PL/pgSQL in a string, because quoting SQL gets insane fast:

$ psql
# SELECT 'SELECT ''foo''';
   ?column?
--------------
 SELECT 'foo'
(1 row)

Dollar-strings solve this problem comprehensively. A simple dollar string extends from $$ to its paired closing $$. This is perfectly suited to defining functions:

$ psql
# CREATE OR REPLACE FUNCTION foo() RETURNS text IMMUTABLE LANGUAGE SQL AS $$ SELECT 'foo' $$;
CREATE FUNCTION
# SELECT foo();
 foo
-----
 foo
(1 row)

Dollar-strings also handle the case where your quoted string itself uses dollar-strings, by adding a tag as $tag$. A tagged dollar string runs from the starting $tag$ to its closing $tag$:

$ psql
# CREATE OR REPLACE FUNCTION bar() RETURNS text IMMUTABLE LANGUAGE SQL AS $body$ SELECT $$bar$$ $body$;
CREATE FUNCTION
# SELECT bar();
 bar
-----
 bar
(1 row)

Dollar-strings are also handy outside of defining functions, but less necessary there:

$ psql
# SELECT $quote$He said, 'Give me lots of $$$!'$quote$;
            ?column?
---------------------------------
 He said, 'Give me lots of $$$!'
(1 row)