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.
Greenplum Database captures the following error information in a table 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 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');
For information about managing formatting errors that are logged internally, see the command COPY or CREATE EXTERNAL TABLE in the Greenplum Database Reference Guide.
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;