Can I use copy to add data into a Postgres array datatype

Question

Ive created some CSV files to bulk import into Postgres 9.2 with the COPY command, but I have a problem because one of the fields in the Postgres table is an array datatype.

Is it possible to import data with COPY into an array datatype, i have both integer and text arrays ?

CREATE TABLE artist (
    id                          integer NOT NULL,
    name                        text NOT NULL,
    realname            text,
    urls                        text[],
    namevariations      text[],
    aliases             text[],
    releases            integer[],
    profile             text,
    members             text[],
    groups                      text[],
        data_quality    text
);

In principle yes, but please post some data from your CSV file and any trials and errors. — Patrick
– Patrick, Commented Sep 16, 2015 at 8:54
Which data type exactly? varchar[] or e.g. integer[]? Please edit your question and add the create table statement. — user330315
– user330315, Commented Sep 16, 2015 at 9:25
If you do not post more information, including some sample data, the best possible answer you will get on your question is "Yes". — Patrick
– Patrick, Commented Sep 16, 2015 at 9:26

score 4 · Accepted Answer · 2016-02-16 13:15:42Z

4

Something like this should work:

id,data
1,{1,2,3}
2,{4,5,6}

This assumes numeric values.

If your data contains character values that in turn can contain the delimiter (a , in my example) you need to enclose each value in double quotes.

id,data
1,{foo,bar}
2,{"foo,bar", "bar,foo"}

The second row will put the strings 'foo,bar' and 'bar,foo' as two elements into the array

Edit

When using a comma as the delimiter, the array values must be quoted, e.g.:

id,data
1,"{1,2,3}"
2,"{4,5,6}"

When dealing with strings, you need to use single quotes inside the array:

id,data
1,"{foo,bar}"
2,"{'foo,bar', 'bar,foo'}"

Quoting is not necessary if you use a different delimiter, e.g. |

edited Feb 16, 2016 at 13:15

answered Sep 16, 2015 at 9:29

user330315

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

3 Comments

DataGreed Over a year ago

are you sure there should be no quuotes or other delimeters around curly braces?

Kevin Vasko Over a year ago

@a_horse_with_no_name "When using a comma as the delimiter, the array values must be quoted, e.g.:" You have to quote it AND escape the the internal commas. so for this to work you have to do 1, "{1\,2\,3}". I think you can avoid doing the internal quotes only if you specify WITH FORMAT CSV

Ankur Over a year ago

For me it worked without doble quotes and with escaping internal commas for integer array like - 1,{1\,2\,3} Thanks @KevinVasko for mentioning about this escaping which helped me.

klin · Accepted Answer · 2015-09-16 09:29:31Z

1

Do a test, e.g.:

create table test (id int, intarr int[], textarr text[]);
insert into test values
(1, array[1,2], array['a','b']);
copy test to 'c:\data\test.txt';

truncate test;
copy test from 'c:\data\test.txt';
select * from test;

and check the format of the file:

1   {1,2}   {a,b}

answered Sep 16, 2015 at 9:29

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

Paul Taylor Over a year ago

Thanks but this is using INSERT but I want to bulk load data using COPY

user330315 Over a year ago

@PaulTaylor: what klin was trying to show you is how you can find out for yourself by running those statements and then looking at the generated file

Collectives™ on Stack Overflow

Can I use copy to add data into a Postgres array datatype

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related