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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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;
However, this does not disable
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.
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;