1

I have two tables t1 and t2. I am deleting certain rows of t2 this way:

delete from t2 where expiry < NOW();

There is a column id common to both t1 and t2. When the above statement is executed, I also want to delete corresponding rows in t1 if any (there might be none).

How can this be done?
Is it possible to write a single query for both these operations?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Ashwin
  • 12,691
  • 31
  • 118
  • 190

1 Answers1

2

I see at least three good ways - depending on further details missing in your question. I build on your example:

  • t2 is the mother table
  • t1 the child

1. Foreign key constraint with ON CASCADE DELETE

Test bed:

CREATE TABLE t2 (
  id serial PRIMARY KEY  -- primary or unique key needed
, expiry timestamp
);

CREATE TABLE t1 (
  id int references t1(id) ON DELETE CASCADE  -- ON UPDATE CASCADE, too?
);

Populate tables:

INSERT INTO t2(expiry)
SELECT (now() + g * interval '1h')
FROM   generate_series(1, 10) g;   -- 10 arbitrary rows

INSERT INTO t1(id)
SELECT id FROM t2 WHERE id%2 = 0;  -- pick even numbers for t1

SELECT * FROM t1;

If you delete rows from t2, corresponding rows from t1 are deleted automatically:

DELETE FROM t2 WHERE id IN (1,2,3,4,5);
-- rows 2,4 in `t1` are deleted automatically

To add such a foreign key constraint to existing tables:

ALTER TABLE t1 ADD CONSTRAINT t1_id_fkey FOREIGN KEY (id)
REFERENCES t2 (id) ON DELETE CASCADE;

Of course, a foreign key requires a unique or primary key on t1.id. But you probably have that in a scenario like that.

2. Trigger AFTER DELETE

If the values in t2.id are not unique, or if you have values in t1.id violating a foreign key constraint, you could create a trigger AFTER DELETE instead.

Trigger function:

CREATE OR REPLACE FUNCTION t2_delaft
  RETURNS trigger 
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM t1
   WHERE  t1.id = OLD.id;

   RETURN NULL;  -- AFTER trigger can return NULL
END
$func$

Trigger:

CREATE TRIGGER delaft
AFTER DELETE ON t2
FOR EACH ROW EXECUTE PROCEDURE t2_delaft();

You could also implement a RULE. But I find triggers generally easier to handle.

3. Data modifying CTE

Forgive me for giving the simplest answer last. This works, no matter what - provided you are on PostgreSQL 9.1 or later:

WITH x AS (
    DELETE FROM t1
    WHERE  id IN (1,2,3,4,5)
    RETURNING id
    )
DELETE FROM t2
USING  x
WHERE  t2.id = x.id;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • my foreign key constraint in t1 is like - FOREIGN KEY (b, c) REFERENCES t2 (c1, c2). So I have given the on delete cascade like this - FOREIGN KEY (b, c) REFERENCES t2 (c1, c2) ON DELETE CASCADE. But now before deleting I want to copy the rows that are going to be deleted from the table t1 to another table(a permanent table which has the same columns as t1) and then delete. So what should I give before ON DELETE CASCADE. – Ashwin Jun 16 '12 at 06:11
  • 1
    Don't you think that is a whole different question? – Mark Rotteveel Jun 16 '12 at 07:50
  • @Ashwin: Logic dictates that a fk constraint can only cascade `UPDATE` and `DELETE`. Triggering an automatic backup copy to another table is a different matter altogether. You can find similar questions (and answers) [here on SO](http://stackoverflow.com/q/9547663/939860) or [here on dba.SE](http://dba.stackexchange.com/q/2531/3684). If that does not cover your case, I suggest you post new question. – Erwin Brandstetter Jun 17 '12 at 03:16