Import CSV text array into PostgreSQL 9.2

Question

I have data something like this:

Akhoond,1,Akhoond,"{""Akhund"", ""Akhwan""}",0

pgAdmin's import is rejecting this. What format does the text[] need to be in the CSV?

I also tried this:

Akhoond,1,Akhoond,"{Akhund, Akhwan}",0

Here's the table create:

CREATE TABLE private."Titles"
(
  "Abbrev" text NOT NULL,
  "LangID" smallint NOT NULL REFERENCES private."Languages" ("LangID"),
  "Full" text NOT NULL,
  "Alt" text[],
  "Affix" bit
)
WITH (
  OIDS=FALSE
);

ALTER TABLE private."Titles" ADD PRIMARY KEY ("Abbrev", "LangID");

CREATE INDEX ix_titles_alt ON private."Titles" USING GIN ("Alt");

ALTER TABLE private."Titles"
  OWNER TO postgres;

It's throwing "extra data after the last expected column". I have checked and there are 5 columns in the DB, and 5 in the CSV. — IamIC
– IamIC, Commented Apr 16, 2013 at 11:34
What if you create a dummy table with a row or two of the desired data by hand, then export it from PgAdmin-III? What format does it produce, and does it then import it correctly? If it exports but doesn't import you've found a bug. Frankly I think it's a bug if it doesn't use the same syntax as the standard COPY command anyway. — Craig Ringer
– Craig Ringer, Commented Apr 16, 2013 at 11:38

Craig Ringer · Accepted Answer · 2013-04-16 10:54:00Z

9

The best way to find out is to create a table with the desired values and COPY ... TO STDOUT to see:

craig=> CREATE TABLE copyarray(a text, b integer, c text[], d integer);
CREATE TABLE
craig=> insert into copyarray(a,b,c,d) values ('Akhoond',1,ARRAY['Akhund','Akhwan'],0);
INSERT 0 1
craig=> insert into copyarray(a,b,c,d) values ('Akhoond',1,ARRAY['blah with spaces','blah,with,commas''and"quotes'],0);
INSERT 0 1
craig=> \copy copyarray TO stdout WITH (FORMAT CSV)
Akhoond,1,"{Akhund,Akhwan}",0
Akhoond,1,"{""blah with spaces"",""blah,with,commas'and\""quotes""}",0

So it looks like "{Akhund,Akhwan}" is fine. Note the second example I added showing how to handle commas, quotes spaces in the array text.

This works with the psql \copy command; if it doesn't work with PgAdmin-III then I'd suggest using psql and \copy.

answered Apr 16, 2013 at 10:54

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Import CSV text array into PostgreSQL 9.2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related