COPY (import) data into PostgreSQL array column

arrays postgresql postgresql-8.4
SabreWolfy picture SabreWolfy · Jun 23, 2012 · Viewed 11.5k times · Source

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'

Answer

Clodoaldo Neto picture Clodoaldo Neto · Jun 23, 2012 
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}}"

Related questions

Iterating over integer[] in PL/pgSQL

I am trying to loop through an integer array (integer[]) in a plpgsql function. Something like this: declare a integer[] = array[1,2,3]; i bigint; begin for i in a loop raise notice "% ",i; end loop; return true; end In my actual …

Read More
Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More
How to turn a json array into rows in postgres

I have a json array stored in my postgres database. The json looks like this: [ { "operation": "U", "taxCode": "1000", "description": "iva description", "tax": "12" }, { "operation": "U", "taxCode": "1001", "description": "iva description", "tax": "12" }, { "operation": "U", "taxCode": "1002", "description": "iva description", "tax": "12" } ] Now I need to …

Read More