13

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.

I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been struggling with it.

Will it be something simple like this (not working)?

DELETE FROM link_group WHERE link_reply = NULL;
  • do you have a DDL for everyone to look at? – dizzystar Apr 3 '16 at 17:18
  • Take a look at the MINUS operator. You have to specify a field in links_reply. – Vérace Apr 3 '16 at 17:40
  • DELETE FROM links_group USING links_group AS lg LEFT JOIN links_reply AS lr ON lg.col= lr.some_other_col WHERE links_reply.some_other_col IS NULL – Mihai Apr 3 '16 at 18:52
  • I had a similar question, which also takes concurrency in account. See dba.stackexchange.com/questions/251875. – pbillen Oct 24 at 15:20
16

Quoting the manual:

There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances.

Bold emphasis mine. Using information that is not contained in another table is a tad bit tricky, but there are easy solutions. From the arsenal of standard techniques to ...

... a NOT EXISTS anti-semi-join is probably simplest and most efficient for DELETE:

DELETE FROM link_group lg
WHERE  NOT EXISTS (
   SELECT FROM link_reply lr
   WHERE  lr.which_group = lg.link_group_id
   );

Assuming (since table definitions are not provided) link_group_id as column name for the primary key of link_group.

The technique @Mihai commented works as well (applied correctly):

DELETE FROM link_group lg
USING  link_group      lg1
LEFT   JOIN link_reply lr ON lr.which_group = lg1.link_group_id
WHERE  lg1.link_group_id = lg.link_group_id
AND    lr.which_group IS NULL;

But since the table expression in the USING clause is joined to the target table (lg in the example) with a CROSS JOIN, you need another instance of the same table as stepping stone (lg1 in the example) for the LEFT JOIN, which is less elegant and typically slower.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.