Postgresql – How to disable foreign key constraints in the Postgres dump file

dumpforeign keyinsertpostgresql

I’m using Postgres 9.5. I have created a dump file of INSERTs for three of my tables. To avoid getting foreign key errors, I added the below to the top and bottom of my file (the “…” is where all the INSERTs are) …

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;
ALTER TABLE addresses DISABLE TRIGGER ALL;
ALTER TABLE table1 DISABLE TRIGGER ALL;
ALTER TABLE table2 DISABLE TRIGGER ALL;

…

ALTER TABLE addresses ENABLE TRIGGER ALL;
ALTER TABLE table1 ENABLE TRIGGER ALL;
ALTER TABLE table2 ENABLE TRIGGER ALL;

However, even when i run this file, I get errors like

ERROR:  insert or update on table "table2" violates foreign key constraint "fk_rails_ba656ceafa"
DETAIL:  Key (table1_id)=(f62c5fee-1031-4d5e-a084-9210f052a2d1) is not present in table "table1".

Why am I getting these errors despite disabling foreign keys and more importantly, how can I prevent them? I want to INSERT all the data and then re-enable foreign keys.

Best Answer

I'm not sure what's happening, but you're certainly not giving us enough information. ALTER TABLE DISABLE TRIGGER ALL works fine on disabling FOREIGN KEYS.

test=# CREATE TABLE table1 ( id serial PRIMARY KEY );
CREATE TABLE

test=# CREATE TABLE table2 ( fkey int REFERENCES table1(id) );
CREATE TABLE

test=# INSERT INTO table2 VALUES (1);
ERROR:  insert or update on table "table2" violates foreign key constraint "table2_fkey_fkey"
DETAIL:  Key (fkey)=(1) is not present in table "table1".

test=# ALTER TABLE table2 DISABLE TRIGGER ALL;
ALTER TABLE

test=# INSERT INTO table2 VALUES (1);
INSERT 0 1

I would never suggest anyone DISABLE TRIGGERS though, or make them NOT VALID. They're there to ensure data integrity and disabling them for even brief periods is a recipe for disaster and an anti-pattern in my opinion.

Just pg_dumpall and trim out what you don't need.

Related Question