Postgres won't allow ARRAY syntax in COPY

Question

I have a table with a text array (text[]) type column in it. I want to use the COPY command to copy a CSV in. I'm using Psycopg2's copy capability, but the question is relevant to Postgres in general.

It seems that Postgres only accepts arrays formatted like {"string1","string2","string3"}, not ARRAY['string1', 'string2', 'string3'] (see below). This is a problem because the string escaping in the former format is a huge pain, and Psycopg2's mogrify function outputs arrays in the latter format. Manual escaping in the first format is my last resort, but I really don't want to go there...

Is there any way to make Postgres take the latter format for copying or some other workaround?

Here are my tests:

-- proof that both syntaxes are valid and compare equal
db=# SELECT ARRAY['string1', 'string2', 'string3']::text[] = '{"string1","string2","string3"}'::text[];
 ?column?
----------
 t
(1 row)

-- COPY works with {} syntax
db=# CREATE TEMP TABLE test(words text[]);
CREATE TABLE
db=# COPY test FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"string1","string2","string3"}
>> \.
COPY 1

-- COPY fails with ARRAY syntax
db=# COPY test FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> ARRAY['string1', 'string2', 'string3']
>> \.
ERROR:  malformed array literal: "ARRAY['string1', 'string2', 'string3']"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  COPY test, line 1, column words: "ARRAY['string1', 'string2', 'string3']"

No, I'm passing data into Python's csv writer. mogrify formats for queries, which have different rules (e.g. single quotes around strings). I did try using mogrify just for the array values, but, as I said, it gives me the ARRAY syntax. — sudo
– sudo, Commented Aug 18, 2016 at 19:24

Clodoaldo Neto · Accepted Answer · 2016-08-18 21:23:48Z

3

Make your data a list of tuples:

data = [
    (1, ['a','b']),
    (2, ['c','d'])
]

Create a values syntax template to receive the data tuples:

values_template = ','.join(['%s'] * len(data))

Place it into a copy command:

copy_values = "copy (values {0}) to stdout (format csv)".format(values_template)

Use mogrify to adapt the Python types to Postgresql types:

copy_values = cursor.mogrify(copy_values, data)

copy_expert exports the file:

f = open('file.csv', 'wb')
cursor.copy_expert(copy_values, f, size=8192)
f.close()

edited Aug 18, 2016 at 21:23

answered Aug 18, 2016 at 21:15

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

sudo Over a year ago

Ah, solved! This is a cool workaround. Weird that I never thought of it even though I was debugging by copying values to stdout. I've got a COPY utility in my Python project that writes to a file to be copied, and I'll change it to do it this way.

sudo Over a year ago

One drawback is that this is slower than Python's csv library, at least with my setup (database local on machine).

Boris Schegolev · Accepted Answer · 2016-08-18 19:16:31Z

1

The first test (the proof) is not really correct. In this case this should be the test:

SELECT 'ARRAY["string1", "string2", "string3"]'::text[] = '{"string1","string2","string3"}'::text[]

and that does not work. So I would assume no, this format can not be used to COPY FROM stdin.

answered Aug 18, 2016 at 19:16

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

3 Comments

sudo Over a year ago

The {} syntax expects single quotes surrounding it in a query, and ARRAY does not. SELECT 'ARRAY["string1", "string2", "string3"]'::text[]; returns an error. But {} syntax does not expect single quotes when copying from a CSV.

Boris Schegolev Over a year ago

That is exactly the problem. When you read it from stdin it behaves as if it was a quoted string, so you end up with an error.

sudo Over a year ago

Ah, I see. Thanks. Looks like I'll have to find another way to do this.

Collectives™ on Stack Overflow

Postgres won't allow ARRAY syntax in COPY

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related