Waiting for PostgreSQL 15 – Add HEADER support to COPY text format

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 🙂

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.