2

Simple DELETE with safety LIMIT throws a syntax error:

DELETE FROM system."products" LIMIT 1

ERROR: Syntax ERROR at "LIMIT"

SQL state: 42601

How do I limit the amount of rows to delete?

(PostgresSQL version 9.6)

Daniel W.
  • 31,164
  • 13
  • 93
  • 151

1 Answers1

9

You can use LIMIT only in the SELECT statement.

Try this:

DELETE FROM system."products" WHERE id IN (SELECT id FROM system."products" LIMIT 1)

Otherwise you can have something like this

DELETE FROM system."products" WHERE id bewteen ? and ?

Obviously this can work only if there is in your table an unique column (named id in my examples)!

Renato
  • 2,077
  • 1
  • 11
  • 22
  • `IN()` is evil, is there no other way? – Daniel W. Feb 19 '20 at 15:25
  • @DanielW.: why do you think `IN ()` is evil - especially if it only contains a single value –  Feb 19 '20 at 15:29
  • @a_horse_with_no_name my query contains more than one value, you are discussing things not related to the question – Daniel W. Feb 19 '20 at 15:29
  • 2
    @DanielW.: then post the actual query. If you show us a (over) simplified problem, how do you expect to get an answer for the **real** problem –  Feb 19 '20 at 15:30
  • 1
    @DanielW. Has the table a primary key? – Renato Feb 19 '20 at 15:30
  • You can use a JOIN or a CTE instead of IN(). – Vesa Karjalainen Feb 19 '20 at 15:30
  • @DanielW. and for exactly that problem Renato has given you an answer. –  Feb 19 '20 at 15:32
  • @VesaKarjalainen Imagine I am writing software to detect corruption of data and deleting a limited amount of random (unpredicted) rows is exactly what I want to do. In MySQL, `IN()` should be avoided as much as possible in favour of `EXISTS()`, totally possible Postgres is doing a better job there than MySQL. – Daniel W. Feb 19 '20 at 17:51
  • So while I am asking for a syntactical solution, you keep bashing my semantic intention. – Daniel W. Feb 19 '20 at 17:57
  • this is not a solution if there is no unique column in the table – Daniel Alder Nov 25 '21 at 11:00
  • @DanielAlder, you're right, it needs an unique column. This was also a question I asked. Anyway ... edited! – Renato Nov 25 '21 at 20:58