LIMIT Postgres DELETE [duplicate]

Question

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)

Of course, LIMIT in any query makes much more sense when you have ORDER BY — Vesa Karjalainen
– Vesa Karjalainen, Commented Feb 19, 2020 at 15:25
@a_horse_with_no_name I thought my query is simple ANSI SQL working in all ANSI compatible systems — Daniel W.
– Daniel W., Commented Feb 19, 2020 at 15:27
There is no LIMIT in ANSI SQL (not for DELETE, nor for any other SQL command) — user330315
– user330315, Commented Feb 19, 2020 at 15:28

Renato · Accepted Answer · 2021-11-25 20:58:59Z

11

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)!

answered Feb 19, 2020 at 15:24

Renato

2,1871 gold badge13 silver badges25 bronze badges

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

11 Comments

IN() is evil, is there no other way?

@DanielW.: why do you think IN () is evil - especially if it only contains a single value

@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

@DanielW. Has the table a primary key?

this is not a solution if there is no unique column in the table

|