Waiting for PostgreSQL 17 – Add new COPY option LOG_VERBOSITY.

On 1st of April 2024, Masahiko Sawada committed patch:

Add new COPY option LOG_VERBOSITY.
This commit adds a new COPY option LOG_VERBOSITY, which controls the
amount of messages emitted during processing. Valid values are
'default' and 'verbose'.
This is currently used in COPY FROM when ON_ERROR option is set to
ignore. If 'verbose' is specified, a NOTICE message is emitted for
each discarded row, providing additional information such as line
number, column name, and the malformed value. This helps users to
identify problematic rows that failed to load.
Author: Bharath Rupireddy
Reviewed-by: Michael Paquier, Atsushi Torikoshi, Masahiko Sawada
Discussion: https://www.postgresql.org/message-id/CALj2ACUk700cYhx1ATRQyRw-fBM%2BaRo6auRAitKGff7XNmYfqQ%40mail.gmail.com

It looks that COPY in PostgreSQL 17 will be more powerful. We got support for error handling, and now we got also support for live notifications of problems.

So, let's see it.

I have test table named x:

=$ \d x
                 TABLE "public.x"
 i      | INTEGER |           |          | 
 y      | INTEGER |           | NOT NULL | 50

And I made test input, that contains some broken data:

=$ printf '1\t2\n2\tb\n3\t4\n5\tz\n' | tee /tmp/input.file
1       2
2       b
3       4
5       z

If I'd try to load it using COPY, I'd get an error, and nothing would be loaded:

=$ copy x (i, y) FROM '/tmp/input.file';
ERROR:  invalid INPUT syntax FOR TYPE INTEGER: "b"
CONTEXT:  COPY x, line 2, COLUMN y: "b"
 i | y 
(0 ROWS)

I could, as I described earlier add on_error IGNORE option, which I will do, but I will also add LOG_VERBOSITY:

=$ copy x (i, y) FROM '/tmp/input.file' ( on_error IGNORE, log_verbosity verbose );
NOTICE:  skipping ROW due TO DATA TYPE incompatibility at line 2 FOR COLUMN y: "b"
NOTICE:  skipping ROW due TO DATA TYPE incompatibility at line 4 FOR COLUMN y: "z"
NOTICE:  2 ROWS were skipped due TO DATA TYPE incompatibility
 i | y 
 1 | 2
 3 | 4
(2 ROWS)

Pretty cool. Thanks to everyone involved 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

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