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?

shafikshaon · Accepted Answer · 2019-06-21 09:37:56Z

60

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, 2019 at 9:37

answered Jun 21, 2019 at 9:27

shafikshaon

6,5025 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Clueless_Coder Over a year ago

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.

Laurenz Albe Over a year ago

If you want to do that, drop the foreign key constraint.

jayarjo Over a year ago

Can you elaborate on your final update? What DISABLE TRIGGER ALL does. Doesn't seem to change anything.

EOhm Over a year ago

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.

Hakan Usakli · Accepted Answer · 2019-10-08 18:02:15Z

14

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;

answered Oct 8, 2019 at 18:02

Hakan Usakli

6227 silver badges13 bronze badges

1 Comment

The Coder Over a year ago

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!

mr.moto_grapher · Accepted Answer · 2022-07-25 10:28:34Z

11

you can truncate all related tables using the command

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

answered Jul 25, 2022 at 10:28

mr.moto_grapher

1191 silver badge2 bronze badges

2 Comments

pixelpax Over a year ago

this didn't work for me for some reason

OJVM Nov 13 at 20:20

This is the correct answer; it works like a charm, TRUNCATE TABLE public.alta, public.orden RESTART IDENTITY;

Mahdi mehrabi · Accepted Answer · 2022-04-18 23:52:23Z

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

edited Apr 18, 2022 at 23:52

answered Apr 18, 2022 at 23:25

Mahdi mehrabi

1,7522 gold badges22 silver badges23 bronze badges

Comments

ctholho · Accepted Answer · 2024-07-20 21:00:41Z

While some of these answers offered you a solution, nobody answered your puzzlement as to why SET CONSTRAINTS ALL DEFERRED doesn't delay the foreign key constraint check.

The docs state about the TRUNCATE command:

TRUNCATE cannot be used on a table that has foreign-key references from other tables, unless all such tables are also truncated in the same command. Checking validity in such cases would require table scans, and the whole point is not to do one.

So, the reason TRUNCATE can't defer the validity check, is because the developers decided it that way.

Because you've said that using CASCADE is no option, I assume that deleting the referenced table is also no option.

You can either use DELETE to defer the check.

Or, more radical, drop the foreign key constraint in question and add it back in later.

nozem · Accepted Answer · 2020-03-26 08:53:21Z

0

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

answered Mar 26, 2020 at 8:53

nozem

5068 silver badges11 bronze badges

Collectives™ on Stack Overflow

Postgresql truncate table with foreign key constraint

6 Answers 6

4 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related