hstore value with space

Question

I am using Postgresql V 9.1:

SELECT 'k=>name, v=>Nava Ratna Secondary School'::hstore;

This code returns error:

ERROR:  Syntax error near 'R' at position 17
LINE 1: SELECT 'k=>name, v=>Nava Ratna Secondary School'::hstore;
               ^

********** Error **********

ERROR: Syntax error near 'R' at position 17
SQL state: XX000
Character: 8

And filling spaces in 'Nava Ratna Secondary School' as 'Nava_Ratna_Secondary_School' or removing them 'NavaRatnaSecondarySchool' works.

But I need to store the v=Nava Ratna Secondary School with spaces. How to do it?

Answer 1

SELECT 'k=>name, v=>"Nava Ratna Secondary School"'::hstore;

When there are single quotes in the values or in the keys the easiest is to use dollar quote:

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

Or the more widespread escaping:

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