postgresql - hstore 带空格的值

Question

我正在使用 Postgresql V 9.1:

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

此代码返回错误:

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

并将“Nava Ratna Secondary School”中的空格填充为“Nava_Ratna_Secondary_School”或将其删除“NavaRatnaSecondarySchool”。

但我需要用空格存储 v=Nava Ratna Secondary School。怎么做？

Answer 1

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

当值或键中有单引号时，最简单的方法是使用美元引号:

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

或者更广泛的转义:

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