COPY (import) data into PostgreSQL array column

Question

How should a (CSV?) text file be formatted so that it can be imported (with COPY?) into an array column in a PostgreSQL (8.4) table?

Given table testarray:

 Column  |          Type           |
---------+-------------------------|
 rundate | date                    | 
 runtype | integer                 | 
 raw     | double precision[]      | 
 labels  | character varying(16)[] | 
 results | double precision[]      | 
 outcome | character varying(8)[]  |

and

COPY testarray from '/tmp/import.txt' CSV

neither of the following contents of import.txt work:

2010/06/22,88,{{1,2},{3,4}},{{1,2},{3,4}},{{1,2},{3,4}},{{1,2},{3,4}}
2010/06/22,88,1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4
2010/06/22,88,'{{1,2},{3,4}}','{{1,2},{3,4}}','{{1,2},{3,4}}','{{1,2},{3,4}}'
2010/06/22,88,'1,2,3,4','1,2,3,4','1,2,3,4','1,2,3,4'

Clodoaldo Neto · Accepted Answer · 2012-06-23 14:28:14Z

29

COPY testarray from '/tmp/import.txt' CSV

2010-06-22,88,"{{1,2},{3,4}}","{{1,2},{3,4}}","{{1,2},{3,4}}","{{1,2},{3,4}}"

answered Jun 23, 2012 at 14:28

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

kgrittn Over a year ago

Note that this is the default format, but you can choose to override the default quote character. postgresql.org/docs/8.4/interactive/sql-copy.html

Collectives™ on Stack Overflow

COPY (import) data into PostgreSQL array column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related