'Postgresql truncate table with foreign key constraint

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?

postgresql


Solution 1:[1]

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;

Solution 2:[2]

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;

Solution 3:[3]

I ended up here because my Sqitch data revert script used TRUNCATE. A workaround that may not be ideal for everyone (but worked for me):

DELETE FROM tableA WHERE id IN (
  '5b50e44a-e8dd-4950-8708-588d6620d578',
  'd1857bfa-43bc-4284-8a49-c6ceba80a66f',
  '3ddff193-eb9f-495b-b63d-87b4e0637caa'
  --etc
);

Solution 4:[4]

this works for me so nice

TRUNCATE table_name RESTART IDENTITY CASCADE;

I used RESTART IDENTITY which resets the sequences associated with the table columns

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Hakan Usakli
Solution 3 nozem
Solution 4

Related Questions