Handling Import Errors
Scenarios
Handle errors that occurred during data import.
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 | numeric | 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. | |
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). |
openGauss 2024-09-07 01:01:32