Disable constraints on a table in Postgres

You simply don't. Just load to a staging table without constraints and do correct load afterwards.

I do agree with what ste5an said:
The only correct way is to load the child tables in the correct order to ensure integrity.

Another possibility is make the FKs deferrable so integrity isn't validated until commit.  That has its own issues as well depending on the apps that use the database.

Hi,
Referential Integrity is implemented using triggers in Postgres.
To disable issue

ALTER TABLE tbl_mytable DISABLE TRIGGER ALL;

Select allOpen in new window

And enable it again

ALTER TABLE tbl_mytable ENABLE TRIGGER ALL; 

Select allOpen in new window

To validate all FK constraints in your schema and/or table you can use this script

do $$
  declare r record;
BEGIN 
FOR r IN  (
  SELECT FORMAT(
    'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc  
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE' 
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
  WHERE  constraint_type = 'FOREIGN KEY' 
    AND tc.constraint_schema = 'YOURSCHEMANAME' and tc.table_name = 'YOURTABLENAME'
)
  LOOP
    EXECUTE (r.x);  
  END LOOP;
END;
$$;

Select allOpen in new window

Best regards,

     Tomas Helgi

We are on Amazon RDS and when I tried to disable the triggers, it throws an error

ERROR: permission denied: "RI_ConstraintTrigger_a_654643" is a system trigger.

Looked up an article and tried changing the below parameter, but still getting the same error msg.

set session_replication_role to replica;