Take the 2-minute tour ×
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's 100% free, no registration required.

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.

share|improve this question
    
So do you have your answer? –  Erwin Brandstetter Dec 3 at 2:02

1 Answer 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;
share|improve this answer
    
very very thanks to "Phil" for helping to resolving my problem,but i have to import hundred's of tables into my target database therefore i want to remove all the constraint temporarily and recreate after importing data to my target database so that i can easily perform my operation. –  sharad vishe Mar 30 '13 at 5:30
    
i got the following error while importing--- ERROR: insert or update on table "my_asrcpt_test" violates foreign key constraint "fk_rm_prmstid" –  sharad vishe Mar 30 '13 at 5:32
    
@sharadvishe: Did you read my answer? –  Erwin Brandstetter Mar 30 '13 at 6:29
    
thank u very much Erwin Brandstetter –  sharad vishe Apr 2 '13 at 13:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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