Postgres 15 Adds Copy Header and Matching

Postgres 15 is out, with 183 new features and changes. Many of these features are major, but I always try to highlight one feature that is simple and useful. For Postgres 15, I have chosen additions to the copy command for text output and verification of copy headers.

First, let's talk about copy headers. You might have seen the header option in previous releases of Postgres. The option adds column names to the first line of the copy output file. Until Postgres 15, the header option was only supported for csv output. Postgres 15 adds this ability to the plain text copy output.

Let's look at an example — first, we create a table and insert two rows — then we show the output without and with headers:

CREATE TABLE copytest (x INTEGER, y TEXT);
 
INSERT INTO copytest VALUES (1, 'My term paper'), (2, 'Crossword puzzle');
 
COPY copytest to STDOUT;
1 My term paper
2 Crossword puzzle
 
COPY copytest to STDOUT WITH (HEADER);
x y
1 My term paper
2 Crossword puzzle

However, just like csv, if you create copy output that includes headers, you must also import the file by specifying header. Failure to do so will either cause the header line to be imported as a data row (if all the columns accept text input), or generate an error. Let's look at an example of that:

COPY copytest TO '/tmp/p' WITH (HEADER);
DELETE FROM copytest;
 
COPY copytest FROM '/tmp/p';
ERROR: invalid input syntax for type integer: "x"
CONTEXT: COPY copytest, line 1, column x: "x"
 
COPY copytest FROM '/tmp/p' WITH (HEADER);
 
SELECT * FROM copytest;
x | y
---+------------------
1 | My term paper
2 | Crossword puzzle

We first created a copy output file /tmp/p with headers. You can see that the first attempt to load the file without specifying header led to an error, but the second attempt with header succeeded.

Another new feature, which is supported by text and csv formats, is the ability to verify that the column names specified in the header line match the input table's column names, in order. This option is designed to reduce the likelihood of accidentally loading a copy file into the incorrect table. Here is an example of that:

ALTER TABLE copytest RENAME y TO z;
 
COPY copytest FROM '/tmp/p' WITH (HEADER);
 
COPY copytest FROM '/tmp/p' WITH (HEADER MATCH);
ERROR: column name mismatch in header line field 2: got "y", expected "z"
CONTEXT: COPY copytest, line 1: "x y"

You can see that since we renamed the second column, the copy header line no longer matches the table's column names, so an error is generated when match is specified.

As you can see, these are not major features, but incremental improvements to existing commands that improve their usability. This is one of the things that helps make Postgres great—small improvements can come from anyone and make a big impact on the usefulness of Postgres.