Effective Duplication of DELETE … LIMIT in PostgreSQL

deletepostgresql

I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable WHERE COND LIMIT X, but in Postgresql this is clearly not an option.

In some cases I do need to delete the last N records; in other cases it doesn't matter as long as they match the overall criteria. Other than primary key and timestamp, the records are identical.

So what is an (elegant) means of duplicating this functionality? It was suggested that I could do a join on ctid, but that doesn't strike me as an especially "nice" solution; is there something cleaner–and more portable–that I can do instead?

Best Answer

It's easy with window functions. Say you want to delete the last @N rows when ordered by some_column. You assign a ROW_NUMBER() with a CTE or a derived table and then DELETE:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  ) 
DELETE FROM theTable
USING del 
WHERE theTable.pk = del.pk         --- the PRIMARY KEY of the table
  AND del.rn <= @N ;               --- your parameter here

or without the USING, in a more standard way:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  ) 
DELETE FROM theTable
WHERE pk IN 
      ( SELECT pk FROM del WHERE rn <= @N ) ;

Test at SQLFiddle.com

Related Question