Postgresql truncate table with foreign key constraint

Question

Currently I am trying to truncate tables which have foreign key constraint on Postgresql 11.3.

I tried doing this

BEGIN; 
SET CONSTRAINTS ALL DEFERRED;
TRUNCATE tableA;
COMMIT;

but received error

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "xxx" references "tableA".
HINT:  Truncate table "xxx" at the same time, or use TRUNCATE ... CASCADE.

Doesn't SET CONSTRAINTS ALL DEFERRED would turn off the foreign key constraint check? Are there anyway to truncate a table without triggering foreign key constraint check and not involving CASCADE?

Answer 1

you can do the following steps to avoid the foreign key error during truncate

1. create automated script that DROPS all foreign keys and constraints (do NOT run it yet)

2. create automated script that RE-CREATES all foreign keys and constraints

3. Run drop script

4. run normal TRUNCATE your_table

5. run recreate keys script

with these steps the TRUNCATE command runs fine because there are no foreign keys.

the drop and re-create scripts are taken from https://blog.hagander.net/automatically-dropping-and-creating-constraints-131/

DROP SCRIPT:

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" DROP CONSTRAINT "'||conname||'";'
 FROM pg_constraint 
 INNER JOIN pg_class ON conrelid=pg_class.oid 
 INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace 
 ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END,contype,nspname,relname,conname

RECREATE SCRIPT:

SELECT 'ALTER TABLE "'||nspname||'"."'||relname||'" ADD CONSTRAINT "'||conname||'" '||
   pg_get_constraintdef(pg_constraint.oid)||';'
 FROM pg_constraint
 INNER JOIN pg_class ON conrelid=pg_class.oid
 INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace
 ORDER BY CASE WHEN contype='f' THEN 0 ELSE 1 END DESC,contype DESC,nspname DESC,relname DESC,conname DESC;

Answer 2

Remove all data from one table

The simplest form of the TRUNCATE TABLE statement is as follows:

TRUNCATE TABLE table_name;

Remove all data from table that has foreign key references

To remove data from the main table and all tables that have foreign key references to the main table, you use CASCADE option as follows:

TRUNCATE TABLE table_name CASCADE;

Update:

BEGIN;
ALTER TABLE table_name DISABLE TRIGGER ALL;
TRUNCATE TABLE table_name;
ALTER TABLE table_name ENABLE TRIGGER ALL;
COMMIT;