Postgres 15 Adds Copy Header and Matching

October 20, 2022

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:

INSERT INTO copytest VALUES (1, 'My term paper'), (2, 'Crossword puzzle');
COPY copytest to STDOUT;
1 My term paper
2 Crossword puzzle
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:

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.

Share this

Relevant Blogs

pgAdmin User Management in Server Mode

pgAdmin can be deployed as a web application by configuring the app to run in server mode. One can check out server deployment on how to run pgAdmin in...
August 24, 2023

pgAdmin CI/CD

Almost exactly three years ago I wrote a blog on my personal page entitled Testing pgAdmin which went into great detail discussing how we test pgAdmin prior to releases. Back...
August 24, 2023

More Blogs

Highlights from the PostgreSQL 16 Beta Release

The PostgreSQL Global Development Group released PostgreSQL 16 Beta 1 on May 25, 2023. PostgreSQL 16 improves logical replication by enabling replication from standbys as well as the ability to...
June 02, 2023