Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?

Was working with MySQL recently. Noticed that UPDATE command of it has special LIMIT clause which is really useful as for me, e.g.

UPDATE `Product_download` SET `version`='9.2.1' WHERE `product_download_id`=367 LIMIT 1;

9 thoughts on “Do we need LIMIT clause in UPDATE and DELETE statements for PostgreSQL?

  1. I proposed this functionality before, but for a different reason. The example you provided should probably have an index on product_download_id if speed is becoming an issue with such a statement.

    But the scenario I was thinking of was where you want to clear out a huge amount of old data from a massive table, but wanted to do it in batches. Of course this is possible using plenty of other methods, but nothing would be as simple as: DELETE FROM huge_table WHERE data_date < '2012-01-01' LIMIT 1000000;

    This could be run every now and then to minimise load on the system. We wouldn't care which records were deleted in particular, just as long as a lot that met the criteria had been deleted. And we wouldn't require the acquisition of exclusive row-level locks for every candidate row.

    1. On MySQL Cluster, which has hard upper limits for the number of uncommitted row changes, this is even essential to be able to perform certain operations …

      Whether this is an argument for DELETE…LIMIT or against MySQL Cluster is left as an excercise to the reader … ;)

    2. Well, do you know math ?

      Instead of rolling a dice to guess whether your replicates will drop the same rows as your master, you could just use a modulo to delete X% of your rows at once…

      DELETE FROM huge_table WHETE data_date < '2012-01-01' AND id % 100 = 0;
      — Do that 100 time, with modulo increasing

      The MySQL UPDATE/DELETE with LIMIT is one of their best tool to disrupt the query-based replication results.

  2. If you are going to have “limit”, you will need “order by”, otherwise the order will be undefined. Either way, why not simply use a subselect? Like so:

    update users set verified = true where id in (select id from users limit 100)


    delete users where id in (select id from users limit 100)

    1. No, we don’t need ORDER BY. See Thom’s use case. Even if it’s necessary we may add it without any problem.

      You’re right! There are a lot of ways doing this. But this is syntactic sugar.

    2. For the purpose of deleting relative small batches you don’t need (nor want) ‘ORDER BY’. You effectively want to work in “disk order”. And the example using the subselect is the imperfect workaround, it doesn’t really guarantee it’s done in 1 pass.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s