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?

shafik · Answer 1 · 2019-06-21T09:37:56.313

47

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;

edited Jun 21 '19 at 09:37

answered Jun 21 '19 at 09:27

shafik

6,098
5
32
50

6

As I understand using CASCADE will affect related tables too which is something I do not want. I want to truncate a table by ignoring the foreign key constraint which may have relationship with other tables. – Clueless_Coder Jun 21 '19 at 09:33
3

If you want to do that, drop the foreign key constraint. – Laurenz Albe Jun 21 '19 at 09:39
2

Can you elaborate on your final update? What `DISABLE TRIGGER ALL` does. Doesn't seem to change anything. – jayarjo May 26 '20 at 12:37
4

The Update is nonsense. Disabling contraints only disable constraints-checking for the single rows and does not disable the constraint itself which is needed to do a Truncate without cascade. (Truncate by definition, opposed to DELETE FROM, does not do a row by row processing so does only truncate the table - if used with cascade it can work even wih the existing contraint in that it truncates all dependent tables). So the Update would be valid if You would write `DELETE FROM TABLE table_name;` instead of Truncate. – EOhm Sep 25 '20 at 11:26

score 11 · Answer 2 · answered Oct 08 '19 at 18:02

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

create automated script that DROPS all foreign keys and constraints (do NOT run it yet)
create automated script that RE-CREATES all foreign keys and constraints
Run drop script
run normal TRUNCATE your_table
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;

This worked very nicely, although I did have to steps before and after step 4. One step before it to copy data from the target table for truncate (that I wanted to save) into a temporary table. Afterwards I copied the saved data back into the target table, then I also ran a script to update the foreign key table so only the foreign keys that remained in the target table were still set in the related table. Drop and recreate scripts very handy thanks! — The Coder, Apr 24 '20 at 04:44

score 4 · Answer 3 · answered Jul 25 '22 at 10:28

4

you can truncate all related tables using the command

TRUNCATE TABLE table_1 , table_2 ,table_3 <etc...> ;

answered Jul 25 '22 at 10:28

mr.moto_grapher

41
1

this didn't work for me for some reason – pixelpax Nov 05 '22 at 17:42

Mahdi mehrabi · Answer 4 · 2022-04-18T23:52:23.433

2

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

edited Apr 18 '22 at 23:52

answered Apr 18 '22 at 23:25

Mahdi mehrabi

1,486
2
18
21

score 0 · Answer 5 · answered Mar 26 '20 at 08:53

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
);

Postgresql truncate table with foreign key constraint

5 Answers5

Linked