PostgreSQL Escape String Tool: Single Quotes, E-Strings

The pane on the left opens with a value carrying the characters that normally cause the trouble, an apostrophe, a per cent sign, an underscore, a backslash, a line break and three characters outside ASCII. Type over it and the five panes beside it keep up, each with a copy button of its own, so choosing a form is a matter of reading them rather than switching a mode and losing the last one.

Three settings sit above the panes. The first decides whether characters above ASCII travel as \uXXXX inside the E-string or stay as themselves. The dollar tag box takes a tag of your own, and where that tag would close the block early the pane falls back to one that fits and says so underneath. The third picks the character that neutralises % and _ for LIKE, where ! reads better than the default backslash.

Unescape runs the other way, so paste any of the forms back, a LIKE pattern with its ESCAPE clause included, and the pane shows the text a client would have sent.

In application code, none of the five is the first thing to reach for, because a parameterised query hands the value to the driver and the driver sends it outside the SQL text, where no quoting mistake can reach the statement. Quoting by hand earns its place in a seed file, a migration, or a script you are writing out as literal SQL, and there the form depends on what the value carries.

quote_literal and the doubled single quote

A string literal ends at its closing quote, so a value carrying a quote of its own has to spell it out, and SQL spells it by writing the quote twice. O'Brien goes in as 'O''Brien', two characters on the page and one in the column, and quote_literal('O''Brien') returns exactly that. Hand it a value with a backslash and it switches form, so quote_literal('a\b') comes back as E'a\\b', an E-string with the backslash doubled, which means the same thing whichever way the session is set. format('%L', value) produces the identical string for text, and for a null it writes the bare word NULL, as quote_nullable does, where quote_literal returns null itself.

E-strings and standard_conforming_strings

The backslash is where most of the advice online has gone stale. With standard_conforming_strings on, the default since PostgreSQL 9.1, a backslash inside '...' is nothing but a backslash, and length('a\nb') comes back 4. Prefix the literal with E and the old rules return, so length(E'a\nb') is 3 and E'\u00e9' comes out as é. Inside an E-string a quote can be written either way, since E'It\'s' and E'It''s' both read It's, and a lone E'\'' is a single quote character and nothing more.

Dollar quoting and choosing a tag

Between $$ and $$ nothing is an escape at all, so a function body full of apostrophes, backslashes and per cent signs goes in exactly as written, line breaks included. The one thing to watch is whether your delimiter turns up inside the text, because $$a$$b$$ ends the block at the second $$ and fails with a syntax error, while $q$a$$b$q$ returns a$$b untouched. A tag follows the rules of an unquoted identifier without the dollar sign, so it starts with a letter or an underscore, and $1a$ is read as a parameter instead. The pane here picks a tag that does not occur in the value and says so underneath.

LIKE patterns and the ESCAPE clause

% matches any run of characters and _ exactly one, so a value that contains either has to be escaped before it can be searched for literally. The default escape is the backslash, and '100%' LIKE '100\%' is true where '100x' LIKE '100\%' is false. Where the pattern already carries backslashes, ESCAPE '!' names another character, as in '100%' LIKE '100!%' ESCAPE '!', and ESCAPE '' switches escaping off altogether, after which % and _ are wildcards and nothing else. Two escape characters in a row match one literal one, and LIKE is case sensitive where ILIKE is not.

quote_ident for identifiers

A table or column name is an identifier rather than a string, so it takes double quotes and quote_ident instead of quote_literal, and a double quote inside it is doubled the same way, quote_ident('a"b') giving "a""b". Names that need no quoting come back bare, quote_ident('orders') is orders, while a capital letter or a space gets the quotes, and so does a reserved word such as select. Unquoted names fold to lower case, which is why CREATE TABLE Orders leaves you with a table called orders, and SELECT * FROM "Orders" then fails with relation "Orders" does not exist, since the quoted name is a different table that can exist beside it. format('%I', name) is the same function under another spelling.