hstore value with single quote

Question

I asked similar question here for: hstore value with space. And get solved by user: Clodoaldo Neto. Now I have come across next case with string containing single quote.

SELECT 'k=>"name", v=>"St. Xavier's Academy"'::hstore;

I tried it by using dollar-quoted string constant by reading http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

SELECT 'k=>"name", v=>$$St. Xavier's Academy$$'::hstore;

But I couldn't get it right.

How to make postgresql hstore using strings containing single quote? It seems like there are more such exceptions possible for this query. How to address them all at once?

Answer 1

You can escape the embedded single quote that same way you'd escape any other single quote inside a string literal: double it.

SELECT 'k=>"name", v=>"St. Xavier''s Academy"'::hstore;
-- ------------------------------^^

Alternatively, you could dollar quote the whole string:

SELECT $$k=>"name", v=>"St. Xavier's Academy"$$::hstore;

Whatever interface you're using to talk to PostgreSQL should be taking care of these quoting and escaping issues. If you're using manual string wrangling to build your SQL then you should be using your driver's quoting and placeholder methods.

---

hstore's internal parsing understands double quotes around keys:

Double-quote keys and values that include whitespace, commas, =s or >s.

Dollar quoting is, as you noted, for SQL string literals, hstore's parser doesn't know what they mean.