postgresql: Force Quotes on Header

Question

I try to Force Quotes on the Header.

Copy (
    SELECT *
    FROM "table"
)
TO <path> CSV HEADER FORCE QUOTE *;

With this, I get the header and all is in quotes except the header. So I want to have the exact opposite.

TO <path> CSV HEADER FORCE QUOTE HEADER;

and

TO <path> CSV FORCE QUOTE HEADER;

did not work.

Any idea how to manage this?

Answer 1

No such option is available in PostgreSQL's COPY support, at least in 9.2 and older. As you've already observed, the headers aren't quoted - or rather, they're always in auto-quote mode so they're only quoted if the contain a delimiter or other special character:

\copy (SELECT 1 AS "First Value", 2 AS "Second value", 3 AS "Third value, with comma") TO '/tmp/test.csv' WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *);

produces:

First Value,Second value,"Third value, with comma"
"1","2","3"

You will need to transform the CSV via a more flexible external tool that understands all the weird and wonderful favours of CSV in use, or use an external tool to produce it directly.

In situations like this I usually write a simple Perl or Python script that queries the database using perl's DBI and DBD::Pg or Python's psycopg2 and uses the appropriate CSV library to output the desired CSV dialect. Such scripts tend to be simple and they're generally more efficient than \copying the CSV then parsing and rewriting it.