Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ Best Practices/ Best Practices for Import and Export Using COPY/ Typical Scenarios/ Importing Erroneous Data Through Error Tolerance
Updated on 2025-05-29 GMT+08:00
View PDF
Share

Importing Erroneous Data Through Error Tolerance

Both the COPY and \COPY commands will halt the data import process upon detecting any data exceptions. To overcome this limitation, GaussDB provides two error tolerance modes: intelligent correction mode and strict verification mode. The strict verification mode (Level 1 error tolerance) is preferred because it can skip abnormal records, maintaining data integrity and minimizing the impact on import performance. For details, see Guide to Importing Erroneous Data.

Below are import commands executed in strict verification mode (Level1 error tolerance):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
-- CSV format
\COPY {data_destination} from '/path/export.txt' log errors reject limit '{limit_num}' encoding {file_encoding} CSV;
-- BINARY format
\COPY {data_destination} from '/path/export.txt' log errors reject limit '{limit_num}' encoding {file_encoding} BINARY;
-- FIXED format
\COPY {data_destination} from '/path/export.txt' log errors reject limit '{limit_num}' encoding {file_encoding} FIXED;
-- TEXT format
\COPY {data_destination} from '/path/export.txt' log errors reject limit '{limit_num}' encoding {file_encoding};
-- data_destination can only be a table name.
-- file_encoding indicates the encoding format used during binary file export.
-- limit_num specifies the maximum number of erroneous rows that the COPY FROM statement can tolerate during data import. If this limit is exceeded, errors will be reported as usual according to the original mechanism.

To use the error tolerance feature, regular users require permissions on the two system catalogs of the feature. Execute the following SQL statements to grant them these permissions:

1
2
 
GRANT INSERT,SELECT,DELETE ON Pgxc_copy_error_log TO {user_name};
GRANT INSERT,SELECT,DELETE ON gs_copy_summary TO {user_name};
Parent topic: Typical Scenarios