syntax for inserting hstore arrays in PostgreSQL

Question

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.

mu is too short · Accepted Answer · 2014-10-28 05:32:22Z

5

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.

edited Oct 28, 2014 at 5:32

answered Oct 28, 2014 at 3:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xiaolong Over a year ago

Thanks! this helps! It would be ideal to have just a string here instead of an 'Array' structure. I have some existing code which requires a string.

mu is too short Over a year ago

Have a look at my update. It is possible but it is a bit of a horrific mess.

xiaolong Over a year ago

^ Thanks! Just figured out, I threw away the inner double quotes and it suddenly worked. Just by looking at your update, I believe it would work as well!

mu is too short Over a year ago

The hstore-quotes are usually only needed with "odd looking" keys. But, if you're generating all this stuff programatically, you'd want to throw in all the quotes and escaping and whatnot just in case.

Collectives™ on Stack Overflow

syntax for inserting hstore arrays in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related