Handling Import Errors

Scenarios

Handle errors that occurred during data import.

Querying Error Information

Errors that occur when data is imported are divided into data format errors and non-data format errors.

Data format errors

When creating a foreign table, specify **LOG INTO **error_table_name. Data format errors during data import will be written into the specified table. You can run the following SQL statement to query error details:

openGauss=# SELECT * FROM error_table_name;

Table 1 lists the columns of the error_table_name table.

Table 1 Columns in the error information table

Column Name	Type	Description
nodeid	integer	ID of the node where an error is reported
begintime	timestamp with time zone	Time when a data format error was reported
filename	character varying	Name of the source data file where a data format error occurs
rownum	bigint	Number of the row where a data format error occurs in a source data file
rawrecord	text	Raw record of a data format error in the source data file
detail	text	Error details

Non-data format errors
A non-data format error leads to the failure of an entire data import task. You can locate and troubleshoot a non-data format error based on the error message displayed during data import.

Handling Data Import Errors

Troubleshoot data import errors based on obtained error information and descriptions in the following table.

Table 2 Handling data import errors

Error Message	Cause	Solution
missing data for column "r_reason_desc"	The number of columns in the source data file is less than that in the foreign table. In a TEXT-format source data file, an escape character (for example, \) leads to delimiter or quote mislocation. Example: The target table contains three columns, and the following data is imported. The escape character (\) converts the delimiter (\|) into the value of the second column, causing the value of the third column to lose. BE\|Belgium\\|1	If an error is reported due to missing columns, perform the following operations: Add the value of the r_reason_desc column to the source data file. When creating a foreign table, set the parameter fill_missing_fields to on. In this way, if the last column of a row in the source data file is missing, it will be set to NULL and no error will be reported. Check whether the row where an error is reported contains the escape character (\). If the row contains such a character, you are advised to set the parameter noescaping to true when creating a foreign table, indicating that the escape character (\) and the characters following it are not escaped.
extra data after last expected column	The number of columns in the source data file is greater than that in the foreign table.	Delete extra columns from the source data file. When creating a foreign table, set the parameter ignore_extra_data to on. In this way, if the number of columns in the source data file is greater than that in the foreign table, the extra columns at the end of rows will not be imported.
invalid input syntax for type numeric: "a"	The data type is incorrect.	In the source data file, change the data type of the columns to import. If this error information is displayed, change the data type to numeric.
null value in column "staff_id" violates not-null constraint	The not-null constraint is violated.	In the source data file, add values to the specified columns. If this error information is displayed, add values to the staff_id column.
duplicate key value violates unique constraint "reg_id_pk"	The unique constraint is violated.	Delete duplicate rows from the source data file. Run the SELECT statement with the DISTINCT keyword to ensure that all imported rows are unique. openGauss=# INSERT INTO reasons *SELECT DISTINCT FROM** foreign_tpcds_reasons;
value too long for type character varying(16)	The column length exceeds the upper limit.	In the source data file, change the column length. If this error information is displayed, reduce the column length to no greater than 16 bytes (VARCHAR2).