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;

Answer 1

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.