Dollar quoting

A special syntax for quoting strings

Dollar quoting is a special syntax for quoting strings to avoid issues related to escaping characters such as single quotes and backslashes.

Dollar quoting was added in PostgreSQL 8.0.

Usage

In standard SQL, single quotes contained within a string constant need to be doubled so the string can be parsed correctly, e.g.:

postgres=# SELECT 'PostgreSQL''s powerful string functions';
                ?column?                
----------------------------------------
 PostgreSQL's powerful string functions
(1 row)

While this may not be so problematic for short strings, it quickly becomes an issue with longer texts. Dollar quoting provides a way round this by enclosing the text in a pair of dollar signs ($$):

postgres=# SELECT $$PostgreSQL's powerful string functions$$;
                ?column?                
----------------------------------------
 PostgreSQL's powerful string functions
(1 row)

Text contained within dollar quotes is treated as a string literal, i.e. does not need to be escaped (and there is no escaping which can be applied).

Tags

An optional "tag" can of one or more arbitrary characters can be provided to create a unique pair of quotes (and is required if the string is intended to contain a literal which could be interpreted as a dollar quote), e.g.:

postgres=# SELECT $foo$PostgreSQL's dollar quoting with '$$' or e.g. '$bar$' is convenient $foo$;
                               ?column?                               
----------------------------------------------------------------------
 PostgreSQL's dollar quoting with '$$' or e.g. '$bar$' is convenient 
(1 row)

The tag may be any valid character sequence which follows the same rules as an unquoted identifier, i.e. apart from underscores (_) may not contain ASCII symbols including the dollar sign ($) itself, and may not start with an ASCII numeral.

The following represent valid examples of dollar quote tags:

  • $$abc$$
  • $$abc123$$
  • $$_$$
  • $$ほげ$$
  • $$1$$ (double-width character)
  • $$;$$ (double-width character)

The following represent invalid examples of dollar quote tags:

  • $$123abc$$
  • $$;$$
  • $$abc+$$
  • $$ $$ (space)

Note many of these will not be interpreted by psql.

Change history