Syntax for inserting hstore arrays in PostgreSQL
New to Postgres, just wondering what the syntax would look like. For example, I have the following table:
CREATE TABLE test
(
field1 hstore[],
field2 text[],
field3 hstore
)
...
For inserting arrays, the syntax is like
INSERT INTO test (field2) VALUES (' {"abc","def"} ');
and for hstore insert the syntax is like
INSERT INTO test (field3) VALUES (' "a"=>1.0, "b"=>2.4 ');
but ,, for inserts in field field1 ', what should I do? Something like below is giving me errors:
INSERT INTO test (field1)
VALUES (`{'"a"=>1.0, "b"=>2.0', '"a"=>3.0, "b"=>4.0' }`)
Any fixes? Thank!
== == EDIT
I just figured it out.
INSERT INTO test (field1)
VALUES ('{"a=>1.0, b=>2.0", "a=>3.0, b=>4.0"}' )
The answer below also helps, but in this particular case the string (instead of an Array structure) works better with my existing code.
source to share
I think you will find it much easier with the array constructor syntax :
You can also use the constructor syntax
ARRAY
:INSERT INTO sal_emp VALUES ('Bill', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
Something like that:
INSERT INTO test (field1)
VALUES (array['"a"=>1.0, "b"=>2.0'::hstore, '"a"=>3.0, "b"=>4.0'::hstore]);
You only need it ::hstore
for the first element in the array, but that doesn't stop you from dropping them all.
I use the array constructor syntax solely because all the string parsing and quoting is giving me a headache.
If you can't use the array constructor syntax, you might ask yourself as PostgreSQL:
=> select array['"a"=>1.0, "b"=>2.0'::hstore, '"a"=>3.0, "b"=>4.0'::hstore];
array
---------------------------------------------------------------------
{"\"a\"=>\"1.0\", \"b\"=>\"2.0\"","\"a\"=>\"3.0\", \"b\"=>\"4.0\""}
Note that the individual hstores are enclosed in double quotes:
"\"a\"=>\"1.0\", \"b\"=>\"2.0\""
and that they use backslash double quotes for their internal structure. This gives us:
INSERT INTO test (field1)
VALUES ('{"\"a\"=>\"1.0\", \"b\"=>\"2.0\"","\"a\"=>\"3.0\", \"b\"=>\"4.0\""}');
I'll try to use the array constructor syntax anyway, all those nested quotes and escape files are nasty.
source to share