Help Center/ GaussDB/ Developer Guide(Centralized_2.x)/ Importing Data/ Running the COPY FROM STDIN Statement to Import Data/ Handling Import Errors
Updated on 2023-10-23 GMT+08:00
View PDF
Share

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:

    1
    		 
    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"
  1. The number of columns in the source data file is less than that in the foreign table.
  2. 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
  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.
  2. 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. 
    1
    		 
    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).