Here is a recreation of the problem statement.

The database schemas for two tables (say Tab1 and Tab2) have been given in a file called (say) schemas.ddl. Multiple integrity constraints (including foreign keys) exist between these two tables. The data of these two tables has been given in data1.csv and data2.csv respectively. I am required to get the data from these csv files into the tables.

This is how I do it:

import psycopg2

conn = psycopg2.connect(database=postgres, user=postgres, host=localhost, port=5432)
cur = conn.cursor()

# Execute the schema, this works fine
cur.execute(open(schemas.ddl, "r").read())

files = ["data1.csv", "data2.csv"]

# Iterate through files to add data
for i in range(1,3):
    copy_sql = "copy Tab" str(i) " from stdin with csv header delimiter as ',' null as 'null'"
    with open(files[i-1],'r') as f:
        cur.copy_expert(sql=copy_sql, file=f)

That is, I use copy_expert() as shown to copy the first file's data and then the second file's data. This however causes foreign key constraints (and other custom constraints) to be violated as the data is entered.

Is there a way to check the integrity of the entered data at commit, and not after every command? I've tried cur.execute("SET CONSTRAINTS ALL DEFERRED;") but that doesn't change anything.

Solution

by Anand Sowmithiran

Just make the foreign keys and the constraints as deferrable. Example:

Foreign Key(A) references B deferrable;

Or if you trust the data to be correct, just disable all triggers and re-enable them after copying.

CodePudding user response：

Those FK constraints must be created with DEFERRABLE initially, only then it can be deferred by using the SET command. Alternatively, you can turn off all triggers on those 2 tables and then do the bulk copy operation, and then enable the triggers. PG uses triggers to enforce FK constraints.

ALTER TABLE tab2 DISABLE TRIGGER ALL;
 --run your program---
--and then enable triggers
ALTER TABLE tab2 ENABLE TRIGGER ALL;