1

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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.