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; ```

Accepted Answer

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

Import CSV text array into PostgreSQL 9.2

Solution

Problem