Delete using left outer join in Postgres

Question

I am switching a database from MySQL to Postgres SQL. A select query that worked in MySQL works in Postgres but a similar delete query does not.

I have two tables of data which list where certain back-up files are located. Existing data (ed) and new data (nd). This syntax will pick out existing data which might state where a file is located in the existing data table, matching it against equal filename and path, but no information as to where it is located in the new data:

SELECT ed.id, ed.file_name, ed.cd_name, ed.path, nd.cd_name
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

I wish to run a delete query using this syntax:

DELETE ed
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

I have tried DELETE ed and DELETE ed.* both of which render syntax error at or near "ed". Similar errors if I try without the alias of ed. If I attempt

DELETE FROM tv_episodes AS ed
LEFT  JOIN data AS nd.....

Postgres sends back syntax error at or near "LEFT".

I'm stumped and can't find much on delete queries using joins specific to psql.

possible duplicate of PostgreSQL: how to delete rows using a join — cimmanon
– cimmanon, Commented Jul 22, 2014 at 14:30
Worth checking out the question linked by @cimmanon (regardless of whether it's a duplicate or not) - the NOT EXISTS syntax works better for me in my situation than the answers here. — GregHNZ
– GregHNZ, Commented Jun 27, 2018 at 0:21

Freek de Bruijn · Accepted Answer · 2023-07-29 16:12:45Z

124

As others have noted, you can't LEFT JOIN directly in a DELETE statement. You can, however, self join on a primary key to the target table with a USING statement, then left join against that self-joined table.

Note the self join on tv_episodes.id in the WHERE clause. This avoids the sub-query route provided above.

DELETE FROM tv_episodes
USING tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
   ed.file_name = nd.file_name AND 
   ed.path = nd.path
WHERE
   tv_episodes.id = ed.id AND
   ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

edited Jul 29, 2023 at 16:12

Freek de Bruijn

3,6223 gold badges27 silver badges29 bronze badges

answered Nov 15, 2015 at 18:55

Joshua Burgner

1,2651 gold badge8 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sbeam Over a year ago

thanks for this. this works well in my case with a couple tables of ~100K rows. The "total cost" given by explain for the subquery methods suggested in the other answers was 337448542.97 (!) and took over 9 minutes. This method had a cost of 40464.59 and took 6.7 seconds.

Nishant Over a year ago

Do you think you should provide the self join condition if there was just one join in using... say if it was just file_name that we are joining with?

Stalinko Over a year ago

I missed your "note" and accidentally deleted everything from the table on production website :( it's really non-obvious especially for users coming from MySQL or SQL Server

urmurmur Over a year ago

This is weird example. data table should be in USING clause, and no left join at all

Giles Over a year ago

Thanks, this fixed my issue. Kind of makes a mockery of having the 'USING' syntax in the first place though, I find the "DELETE table FROM table LEFT JOIN..." syntax of, say, mysql, allot more convenient.

|

dwlamb · Accepted Answer · 2014-02-09 20:59:08Z

15

As bf2020 points out, postgres does not support JOINs when conducting a DELETE query. The proposed solution of a sub-query made me think of the solution. Refine the SELECT query from above and employ it as a sub-query to a DELETE query statement:

DELETE FROM tv_episodes 
WHERE id in (
    SELECT ed.id
    FROM tv_episodes AS ed
    LEFT OUTER JOIN data AS nd ON
    ed.file_name = nd.file_name AND 
    ed.path = nd.path
    WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL
);

Sub-queries can often be inefficient consuming time and CPU resources with some database systems, especially MySQL. From my experience I try to avoid using a sub-query due to that inefficiency plus that such queries are sometimes an easy way out to honing one's skill like learning JOIN syntax.

Since postgre does not permit delete queries using join, the above is the solution that works.

edited Feb 9, 2014 at 20:59

answered Feb 9, 2014 at 20:54

dwlamb

9452 gold badges7 silver badges14 bronze badges

2 Comments

Craig Ringer Over a year ago

Postgres does not support JOINs when conducting a DELETE query. Nonsense, it does, though the syntax is weird. DELETE FROM ... USING .... See the manual. However, it doesn't support left-joining against the table to be deleted from, that's true.

bf2020 Over a year ago

@dwlamb - if you aren't accepting my answer you should upvote it because it put you on the right track. meta.stackexchange.com/questions/5234/…

Meow · Accepted Answer · 2018-04-20 09:21:16Z

5

Use the DELETE... USING syntax:

DELETE FROM tv_episodes USING data WHERE 
tv_episodes.file_name = data.file_name AND 
tv_episodes.path = data.path AND 
tv_episodes.cd_name = 'MediaLibraryDrive' AND 
data.cd_name IS NULL;

answered Apr 20, 2018 at 9:21

Meow

1802 silver badges8 bronze badges

1 Comment

Alec Gerona Over a year ago

In my case, I had to do the self join that Joshua Burgner proposed to avoid deleting everything. Luckily I tried it in a testing database first.

bf2020 · Accepted Answer · 2014-02-09 21:56:53Z

3

Instead of

DELETE ed
FROM tv_episodes AS ed
LEFT OUTER JOIN data AS nd ON
ed.file_name = nd.file_name AND 
ed.path = nd.path
WHERE ed.cd_name = 'MediaLibraryDrive' AND nd.cd_name IS NULL;

please try

DELETE FROM tv_episodes
WHERE cd_name = 'MediaLibraryDrive' AND 
  (tv_episodes.filename, tv_episodes.path IN
    (SELECT ed.filename, 
    ed.path 
    FROM tv_episodes AS ed 
    INNER JOIN data AS nd 
      ON ed.file_name = nd.file_name 
        AND ed.path = nd.path
    WHERE nd.cd_name IS NULL)
  )
  ;

JOIN is not valid in a DELETE query according to the postgresql documentation. You might need to concatenate the left and right parts of the IN expression.

edited Feb 9, 2014 at 21:56

answered Feb 9, 2014 at 19:35

bf2020

7324 silver badges7 bronze badges

2 Comments

dwlamb Over a year ago

There were some syntax errors in your solution (a comma before FROM and clear declaration of the JOIN on specific fields). I only note these for someone else that comes across this challenge looking for a solution. Kindly see the above revision of my dilemma above. Your advice gave me the solution.

Craig Ringer Over a year ago

For PostgreSQL a joined delete is done with DELETE FROM ... USING ..., but there's currently no way to express a left outer join against the delete target table AFAIK.

Collectives™ on Stack Overflow

Delete using left outer join in Postgres

4 Answers 4

6 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related