Deleting rows from child table without deleting from parent

All we need is an easy explanation of the problem, so here it is.

I have 2 tables, connected with inheritance.
I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well:

CREATE OR REPLACE FUNCTION earthquakes_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO earthquakes VALUES (NEW.*);
    DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER earthquakes_trigger
        BEFORE INSERT ON earthquakes_ovr
        FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

Parent table : earthquakes_ovr
Child table: earthquakes

I also tried to break up the trigger function, and have the deletion part as trigger function on the child, but that didn’t work either.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As you can see that is not the case, when the tables are not linked in anyway

CREATE tABLE earthquakes_ovr ("datetime" timestamp)

INSERT INTO earthquakes_ovr VALUES (NOW() - INTERVAL '6 DAYS')

INSERT INTO earthquakes_ovr VALUES (now() - INTERVAL'4 days')

CREATE UNLOGGED TABLE  earthquakes AS SELECT * FROM earthquakes_ovr

    CREATE OR REPLACE FUNCTION earthquakes_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO earthquakes VALUES (NEW.*);
        DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
        RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER earthquakes_trigger
            BEFORE INSERT ON earthquakes_ovr
            FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

INSERT INTO earthquakes_ovr VALUES (now())

SELECT * FROM earthquakes_ovr

| datetime                   |
| :------------------------- |
| 2021-04-18 00:11:15.191671 |
| 2021-04-20 00:11:15.203608 |
| 2021-04-24 00:11:15.267616 |

SELECT * FROM earthquakes

| datetime                   |
| :------------------------- |
| 2021-04-24 00:11:15.267616 |

db<>fiddle here

Method 2

While targeting the child table directly (earthquakes in your case), rows form the parent table are not affected. You got that backwards, the ONLY keyword has no effect here (is just noise):

DELETE FROM ONLY earthquakes ...

The manual on DELETE:

If ONLY is specified before the table name, matching rows are deleted from the named table only. If ONLY is not specified, matching rows are also deleted from any tables inheriting from the named table.

ONLY can make sense targeting the parent table (earthquakes_ovr in your case).

Consider a partitioned table instead of inheritance. Then you don’t need to re-route inserts to the parent table manually. The manual on INSERT:

If the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0