Viewing Bad Rows in the Error Table or Error Log


Viewing Bad Rows in the Error Table or 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 either logged internally or logged into an error table.

Note: Storing row formatting in a error table is deprecated in Greenplum Database and will not be supported in a the next major release. Only internal error logs will be supported.

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

Table 1. Error Table Format
column type description
cmdtime timestamptz Timestamp when the error occurred.


errmsg text The error message text.
rawdata text The raw data of the rejected row.

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

SELECT gp_read_error_log('ext_expenses');


If you created an error table, you can use SQL commands to query the error table and view the rows that did not load. For example:

=# SELECT * from err_expenses;