Get column names using COPY query TO STDOUT with psycopg3

Question

How can I get the column names when getting data using psycopg3 with COPY TO STDOUT feature, f.e. copy (select a, b, c, from t1 join t2 using (t1_id) where not t1.is_test) to STDOUT feature? Because this is not simple select from a table, I cannot get table columns' names from metadata.

If I try to do COPY (query) TO STDOUT WITH CSV HEADER (as described here https://stackoverflow.com/a/70598136/1828296) I get error psycopg.DataError: bad copy format: got a newline before the end of the row.

Another approach would be to do regular select query limit 0 and get column names from this result, but if query is quite long/huge, this doubles amount of time/resources required to get results because I have to execute it twice.

Please note:

this will work well in psql, and it seems that this is not a SQL syntax issue, but rather a psycopg3 library issue.

In their doc (psycopg.org/psycopg3/docs/basic/…) they say: you must not specify COPY options such as FORMAT CSV, DELIMITER, NULL: please leave these details alone, thank you. I was wondering if some workaround exists for this.

score 1 · Accepted Answer · answered May 26 '23 at 20:22

From here COPY Copying block-by-block

with open('cp.csv', 'wb') as csv_file:
    with cur.copy("COPY (select c.category, c.descript from cell_per AS cp join category AS c on cp.category = c.category where cell_per = 18) TO STDOUT WITH (FORMAT CSV, HEADER)") as copy:
        for data in copy:
            csv_file.write(data)

cat cp.csv
category,descript
GER SC 3.5,Scented Geraniums 3.5 in.
HERB 3.5,Herbs 3 1/2 in.
H PREM 3.5,Premium Herbs 3 1/2 inch
SUCCULENTS,Succulents

Get column names using COPY query TO STDOUT with psycopg3

1 Answers1