Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

STL_LOADERROR_DETAIL

Displays a log of data parse errors that occurred while using a COPY command to load tables. To conserve disk space, a maximum of 20 errors per node slice are logged for each load operation.

A parse error occurs when Amazon Redshift cannot parse a field in a data row while loading it into a table. For example, if a table column is expecting an integer data type and the data file contains a string of letters in that field, it causes a parse error.

Query STL_LOADERROR_DETAIL for additional details, such as the exact data row and column where a parse error occurred, after you query STL_LOAD_ERRORS to find out general information about the error.

The STL_LOADERROR_DETAIL table contains all data columns including and prior to the column where the parse error occurred. Use the VALUE field to see the data value that was actually parsed in this column, including the columns that parsed correctly up to the error.

This table is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.

Table Columns

Column Name Data Type Description
userid integer ID of the user who generated the entry.
slice integer Slice where the error occurred.
session integer Session ID for the session performing the load.
query integer Query ID. The query column can be used to join other system tables and views.
filename character(256) Complete path to the input file for the load.
line_number bigint Line number in the load file with the error.
field integer Field with the error.
colname character(1024) Column Name.
value character(1024) Parsed data value of the field. (May be truncated.) Multibyte characters in the load data are replaced with a period.
is_null integer Whether or not the parsed value is null.
type character(10) Data Type of the field.
col_length character(10) Column length, if applicable. This field is populated when the data type has a limit length. For example, for a column with a data type of "character(3)", this column will contain the value "3".

Sample Query

The following query joins STL_LOAD_ERRORS to STL_LOADERROR_DETAIL to view the details of a parse error that occurred while loading the EVENT table, which has a table ID of 100133:

Copy
select d.query, d.line_number, d.value, le.raw_line, le.err_reason from stl_loaderror_detail d, stl_load_errors le where d.query = le.query and tbl = 100133;

The following sample output shows the columns that loaded successfully, including the column with the error. In this example, two columns successfully loaded before the parse error occurred in the third column, where a character string was incorrectly parsed for a field expecting an integer. Because the field expected an integer, it parsed the string "aaa", which is uninitialized data, as a null and generated a parse error. The output shows the raw value, parsed value, and error reason:

Copy
query | line_number | value | raw_line | err_reason -------+-------------+-------+----------+---------------- 4 | 3 | 1201 | 1201 | Invalid digit 4 | 3 | 126 | 126 | Invalid digit 4 | 3 | | aaa | Invalid digit (3 rows)

When a query joins STL_LOAD_ERRORS and STL_LOADERROR_DETAIL, it displays an error reason for each column in the data row, which simply means that an error occurred in that row. The last row in the results is the actual column where the parse error occurred.

On this page: