0

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?

2

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

  • Okay, this seems good. Is it going to be especially slow or have any other caveats I should know about? Also, I'd appreciate knowing any other approaches that might work, simply to improve my understanding of SQL. – Vector Gorgoth Mar 5 '14 at 18:14
  • If there is an index on the column you use for the ORDER BY, it's not going to be slow. Of course, that depends on the <conditions> you have as well. – ypercubeᵀᴹ Mar 5 '14 at 18:34
  • I should probably have stipulated that everything is indexed. – Vector Gorgoth Mar 5 '14 at 19:02

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.