Postgres: DELETE hangs on a table with a self-referential foreign key

Question

I am trying to delete rows from a table that has a self-referential foreign key:

CREATE TABLE items (
    id        SERIAL  PRIMARY KEY,
    parent_id INTEGER      NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES refers(id)
);

Now when I simply do:

DELETE FROM items;

the query freezes forever. If I kill it with Ctrl-C, it says

ERROR: canceling statement due to user request CONTEXT: SQL statement "UPDATE ONLY "public"."items" SET "parent" = NULL WHERE $1 OPERATOR(pg_catalog.=) "parent""

How to delete rows from such a table?

Answer 1

According to this answer, foreign keys in Postgres are implemented as triggers, so you can temporarily disable foreign key checks like this:

ALTER TABLE items DISABLE TRIGGER ALL; 
DELETE FROM items; 
ALTER TABLE items ENABLE TRIGGER ALL;