Loading large amount of data into Postgres Hstore

The above answers seems incomplete in that if you try to copy in multiple columns including a column with an hstore type and use a comma delimiter, COPY gets confused, like:

$ cat test1,a=>1,b=>2,a2,c=>3,d=>4,b3,e=>5,f=>6,ccreate table b(a int4, h hstore, c varchar(10));CREATE TABLE;copy b(a,h,c) from 'test' CSV;ERROR:  extra data after last expected columnCONTEXT:  COPY b, line 1: "1,a=>1,b=>2,a"

Similarly:

copy b(a,h,c) from 'test' DELIMITER ',';ERROR:  extra data after last expected columnCONTEXT:  COPY b, line 1: "1,a=>1,b=>2,a"

This can be fixed, though, by importing as a CSV and quoting the field to be imported into hstore:

$ cat test1,"a=>1,b=>2",a2,"c=>3,d=>4",b3,"e=>5,f=>6",ccopy b(a,h,c) from 'test' CSV;COPY 3select h from b;         h          -------------------- "a"=>"1", "b"=>"2" "c"=>"3", "d"=>"4" "e"=>"5", "f"=>"6"(3 rows)

Quoting is only allowed in CSV format, so importing as a CSV is required, but you can explicitly set the field delimiter and quote character to non ',' and '"' values using the DELIMITER and QUOTE arguments for COPY.

both insert and copy appear to work in natural ways for me

create table b(h hstore);insert into b(h) VALUES ('a=>1,b=>2'::hstore), ('c=>2,d=>3'::hstore);select * from b;         h          -------------------- "a"=>"1", "b"=>"2" "c"=>"2", "d"=>"3"(2 rows)$ cat > /tmp/t.tsva=>1,b=>2c=>2,d=>3^dcopy b(h) from '/tmp/t.tsv';select * from b;         h          -------------------- "a"=>"1", "b"=>"2" "c"=>"2", "d"=>"3" "a"=>"1", "b"=>"2" "c"=>"2", "d"=>"3"(4 rows)

You can definitely do this with the copy binary command.

I am not aware of a python lib that can do this, but I have a ruby one that can help you understand the column encodings.

https://github.com/pbrumm/pg_data_encoder