On 28th of January 2022, Peter Eisentraut committed patch:
Add HEADER support to COPY text format The COPY CSV format supports the HEADER option to output a header line. This patch adds the same option to the default text format. On input, the HEADER option causes the first line to be skipped, same as with CSV. Author: Rémi Lapeyre <remi.lapeyre@lenstra.fr> Discussion: https://www.postgresql.org/message-id/flat/CAF1-J-0PtCWMeLtswwGV2M70U26n4g33gpe1rcKQqe6wVQDrFA@mail.gmail.com
Well, there is not much to explain about it, but let's see how that looks like.
For tests, I'll make a simple table:
=$ CREATE TABLE copy_test (id int4, ts timestamptz, payload text); =$ INSERT INTO copy_test (id, ts, payload) VALUES (1, now() - '1 week'::INTERVAL, 'first row'), (2, now(), 'second row');
Since Pg 8.0 we can use CSV format with copy:
=$ copy copy_test TO stdout WITH (format csv); 1,2022-01-21 11:54:32.861131+01,FIRST ROW 2,2022-01-28 11:54:32.861131+01,SECOND ROW
and with csv, we can have header:
=$ copy copy_test TO stdout WITH (format csv, header); id,ts,payload 1,2022-01-21 11:54:32.861131+01,FIRST ROW 2,2022-01-28 11:54:32.861131+01,SECOND ROW
It is important to understand that on load header doesn't matter – Pg doesn't match columns to header – header line is simply ignored.
Anyway – now, without using CSV, we can get header too:
=$ copy copy_test TO stdout WITH (header); id ts payload 1 2022-01-21 11:54:32.861131+01 FIRST ROW 2 2022-01-28 11:54:32.861131+01 SECOND ROW
And, we can also specify with (header) when loading data, and again – it will make Pg to ignore first line.
This will definitely be useful, thanks a lot to all involved 🙂