History

Error logging in COPY was a proposed feature developed by Aster Data against the PostgreSQL 9.0 code base. It was submitted and reviewed (1) but not accepted into the core product for that or any other version so far.

Overview

The purpose of error logging in COPY is to prevent the backend from erroring out if a malformed tuple is encountered during a COPY operation. Bad tuples can either be skipped or logged into an error logging table.

The COPY command returns the number of successfully copied tuples only.

COPY options

Error logging is set by adding options to the COPY command. Here is the list of the available options:

Variable name

Description

Default value

ERROR_LOGGING

Enables error handling for COPY commands (when set to true).

true

ERROR_LOGGING_SKIP_BAD_ROWS

Enables the ability to skip malformed tuples that are encountered in COPY commands (when set to true).

true

ERROR_LOGGING_MAX_ERRORS

Maximum number of bad rows to log before stopping the COPY operation (0 means unlimited).

0

ERROR_LOGGING_SCHEMA_NAME

Schema name of the table where malformed tuples are inserted by the error logging module

'public'

ERROR_LOGGING_TABLE_NAME

Relation name where malformed tuples are inserted by the error logging module. The table is automatically created if it does not exist.

'error_table'

ERROR_LOGGING_LABEL

Optional label that is used to identify malformed tuples

COPY command text

ERROR_LOGGING_KEY

Optional key to identify malformed tuples

Index of the tuple in the COPY stream

Bad tuples can be rejected for a number of reasons (extra or missing column, constraint violation, ...). The error table tries to capture as much context as possible about the error. If the table does not exist it is created automatically. The format of the error logging table is as follows:

tupletimestamp stores the time at which the error occured. targettable describes the table in which the row was inserted when the error occured. The exact error message and sql error code are recorded in errmessage and sqlerrcode, respectively. The original data of the row can be found in rawdata.