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']"

Answer 1

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()