Viewing Bad Rows in the Error Log

Viewing Bad Rows in the Error Log

If you use single row error isolation (see Define an External Table with Single Row Error Isolation or Running COPY in Single Row Error Isolation Mode), any rows with formatting errors are logged internally by Greenplum Database.

Greenplum Database captures the following error information in a table format:

Table 1. Error Log Format
column type description
cmdtime timestampz Timestamp when the error occurred.
relname text The name of the external table or the target table of a COPY command.
filename text The name of the load file that contains the error.
linenum int If COPY was used, the line number in the load file where the error occurred. For external tables using file:// protocol or gpfdist:// protocol and CSV format, the file name and line number is logged.
bytenum int For external tables with the gpfdist:// protocol and data in TEXT format: the byte offset in the load file where the error occurred. gpfdist parses TEXT files in blocks, so logging a line number is not possible.

CSV files are parsed a line at a time so line number tracking is possible for CSV files.

errmsg text The error message text.
rawdata text The raw data of the rejected row.
rawbytes bytea In cases where there is a database encoding error (the client encoding used cannot be converted to a server-side encoding), it is not possible to log the encoding error as rawdata. Instead the raw bytes are stored and you will see the octal code for any non seven bit ASCII characters.

You can use the Greenplum Databasae built-in SQL function gp_read_error_log() to display formatting errors that are logged internally. For example, this command displays the error log information for the table ext_expenses:

SELECT gp_read_error_log('ext_expenses');

For information about managing formatting errors that are logged internally, see the command COPY or CREATE EXTERNAL TABLE in the Greenplum Database Reference Guide.