Loading large amount of data into Postgres Hstore

Question

The hstore documentation only talks about using "insert" into hstore one row at a time. Is there anyway to do a bulk upload of several 100k rows which could be megabytes or Gigs into a postgres hstore.

The copy commands seems to work only for uploading csv files columns

Could someone post an example ? Preferably a solution that works with python/psycopg

Answer 1

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 test
1,a=>1,b=>2,a
2,c=>3,d=>4,b
3,e=>5,f=>6,c

create 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 column
CONTEXT:  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 column
CONTEXT:  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 test
1,"a=>1,b=>2",a
2,"c=>3,d=>4",b
3,"e=>5,f=>6",c

copy b(a,h,c) from 'test' CSV;
COPY 3
select 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.

Answer 2

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.tsv
a=>1,b=>2
c=>2,d=>3
^d

copy 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)