Postgresql – Getting error while importing large data through csv

constraintcsverrorsimportpostgresql

I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.

I want to disable all of the database constraints temporarily – I do not know how to do this.

Best Answer

It really depends which constraints are violated. You neglected to disclose that in your question.

To disable triggers (including foreign key constraints):

ALTER TABLE tbl DISABLE TRIGGER ALL;

Be sure to revert it afterwards:

ALTER TABLE tbl ENABLE TRIGGER ALL;

More in the manual.

However, this does not disable Check, Not-Null, Unique, Primary Key or Exclusion constraints, which are implemented independently.
You could drop and recreate those. But this is rarely useful, constraints are in place for a reason.

Alternative

An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.

CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:

INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;
Related Question