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: 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.

