CODEWITHSUNDEEP
sqlarrayspostgresqlhstore
xiaolong
xiaolong

Reputation: 3647

syntax for inserting hstore arrays in PostgreSQL

New to Postgres, just wondering how the syntax would be like. For example, I have the following table:

CREATE TABLE test
(
  field1 hstore[],
  field2 text[],
  field3 hstore
)
...

For inserting arrays, syntax is like

INSERT INTO test (field2)  VALUES (' {"abc","def"} ');

and for inserting hstore, syntax is like

INSERT INTO test (field3) VALUES (' "a"=>1.0, "b"=>2.4 ');

but,,, for insertions on 'field1', what do I do? Something like below gives me errors:

INSERT INTO test (field1) 
VALUES (`{'"a"=>1.0, "b"=>2.0', '"a"=>3.0, "b"=>4.0' }`)

Any fixes? Thanks!

==EDIT==

Just figured it out. 

INSERT INTO test (field1) 
VALUES ('{"a=>1.0, b=>2.0", "a=>3.0, b=>4.0"}' )

The answer below helps as well, but in this particular case, a string(instead of an Array structure) works better with my existing code.

Upvotes: 3

Views: 8325

Answers (1)

mu is too short
mu is too short

Reputation: 434835

I think you'll have a much easier time with the array constructor syntax:

The ARRAY constructor syntax can also be used:

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

Something like this:

INSERT INTO test (field1) 
VALUES (array['"a"=>1.0, "b"=>2.0'::hstore, '"a"=>3.0, "b"=>4.0'::hstore]);

You only need the ::hstore cast on the first element in the array but it doesn't hurt to cast them all.

I tend to use the array constructor syntax exclusively because all the string parsing and quoting gives me a headache.

If you can't use the array constructor syntax, you can ask PostgreSQL itself how to do it:

=> 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 wrapped in double quotes:

"\"a\"=>\"1.0\", \"b\"=>\"2.0\""

and that they use backslash-escaped double quotes for their internal structure. That gives us:

INSERT INTO test (field1) 
VALUES ('{"\"a\"=>\"1.0\", \"b\"=>\"2.0\"","\"a\"=>\"3.0\", \"b\"=>\"4.0\""}');

I'd still try to use the array constructor syntax, all those nested quotes and escapes are nasty.

Upvotes: 5

Related Questions