Postgresql truncate table with foreign key constraint

truncate table cascade postgres
cannot truncate a table referenced in a foreign key constraint
postgres truncate table if exists
how to rollback truncate table in postgresql
postgresql function truncate table
python postgresql truncate table
postgres truncate partition
knex truncate all tables

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?


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;

PostgreSQL TRUNCATE TABLE with Practical Examples, For example, to remove all data from the invoices table and cascade to any tables that reference to invoices table via foreign key constraints, you use the following  Only the owner of a table may TRUNCATE it. 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.


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;

Documentation: 9.1: TRUNCATE, Automatically truncate all tables that have foreign-key references to any of the and cascade to any tables that reference othertable via foreign-key constraints: A table can have multiple foreign keys depending on its relationships with other tables. In PostgreSQL, you define a foreign key through a foreign key constraint. A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table. We say that a foreign key constraint maintains referential integrity between child and parent tables. Define simple PostgreSQL foreign key constraint


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

DELETE all data really fast with TRUNCATE TABLE CASCADE , CASCADE construct which was introduced in PostgreSQL 8.2, which truncate a table referenced in a foreign key constraint DETAIL: Table  As the sequence below shows, I dropped the FK constraint successfully, but when I run TRUNCATE collections_l it says: ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "client_collect_rates" references "collections_l" via foreign key constraint "$2". HINT: Truncate table "client_collect_rates" at the same time.


PostgreSQL: TRUNCATE TABLE Statement, The PostgreSQL TRUNCATE TABLE statement is a used to remove all records all tables in the foreign key relationship are listed in the TRUNCATE command. 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; For example, to remove all data from the invoices table and cascade to any tables that reference to invoices table via foreign key constraints, you use the following statement: TRUNCATE TABLE invoices CASCADE;


Table truncate ignoring foreign key constraint : PostgreSQL, We tried ` SET CONSTRAINTS ALL DEFERRED;` but still couldn't truncate our tables. Are there anyway in PostgreSQL to turn off the foreign key constraint check  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:


Postgres suggests "Truncate table at the same time", ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "XXXXX" references "YYYYY". HINT: Truncate table "  As the sequence below shows, I dropped the FK constraint successfully, but when I run TRUNCATE collections_l it says: ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "client_collect_rates" references "collections_l" via foreign key constraint "$2". HINT: Truncate table "client_collect_rates" at the same time.