PostgreSQL Escape String Tool
Escapes a value for Postgres the way quote_literal() does, doubling every single quote, and writes the same value beside it as an E-string, a dollar-quoted block, a LIKE pattern and a quoted identifier. The quoting happens in this tab.
E'Ana O''Brien, 50% offC:\\seed_data\\café ☕'E'Ana O\'Brien, 50% off\nC:\\seed_data\\caf\u00E9 \u2615'$$Ana O'Brien, 50% offC:\seed_data\café ☕$$E'Ana O''Brien, 50\\% offC:\\\\seed\\_data\\\\café ☕' ESCAPE E'\\'"Ana O'Brien, 50% offC:\seed_data\café ☕"An unquoted name folds to lower case, so this one is quoted to keep its capitals. Ctrl or Cmd with Enter copies it.
What it does and how to use it
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.
What quote_literal does, and when dollar quoting is easier
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.
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. That is why quote_literal writes an E in front of any value containing a backslash and doubles the backslash behind it, so its output means the same thing whichever way a session is set.
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. The one thing to watch is whether your delimiter turns up inside the text, and the pane picks a tag that does not.
In application code, none of this is the first thing to reach for. A parameterised query hands the value to the driver, which sends it outside the SQL text, so no escaping bug 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.
Frequently asked questions
A string literal ends at its closing quote, so a quote inside the value has to be written twice before the parser reads it as data. quote_literal does that doubling, and format with %L returns the same string.
Only where you want backslash escapes. A plain literal treats a backslash as an ordinary character while standard_conforming_strings is on, so the E is what turns backslash-n back into a newline. quote_literal adds it to any value containing a backslash.
Dollar quoting, once the value is long or already carries quotes, because nothing between the delimiters is interpreted. Shorter values read better with the doubled quote, and both forms run the same way under psql.
No. The quoting is a handful of string passes in this tab, with no request going out and nothing kept. That matters more here than on most of these pages, since the value someone reaches for an escaper with is usually a real one.
The result matches for a text value, though the mechanism is different. A driver normally sends the value as a parameter, outside the SQL text, where no quoting bug can reach the statement. Reach for a parameter wherever your code allows one.
A name is an identifier rather than a string, so it takes double quotes and quote_ident instead of quote_literal. Unquoted names fold to lower case as well, which is why CREATE TABLE Orders leaves you with a table called orders.
It is free and there is nothing to sign up for. Nobody counts the values you put through it, and none of them are stored.
The formatter lays out the statement you paste the literal into, and the syntax checker runs the real PostgreSQL grammar over it and reports where it stopped. Both are linked further down this page.
Other free PostgreSQL tools
- FormatterIndents a Postgres query and puts the keywords where you expect them, so a long CTE stops being one unreadable line. Minifies it again when you need it back on one line.
- Syntax checkerRuns your statement through the grammar Postgres itself uses and reports the same message the server would, with the character position it stopped at.
- PlaygroundA real Postgres server compiled to WebAssembly, running inside the tab. Create tables, insert rows, join them, and read the plan, with no server behind it.
Further reading: The Dump That Breaks Its Own Restore.