Question: How do you limit the number of rows deleted in PostgreSQL?

Answer

PostgreSQL, unlike some other SQL databases, does not directly support the LIMIT clause in DELETE operations through its standard syntax. However, you can still limit the number of rows deleted in a PostgreSQL database by using a workaround involving subqueries or common table expressions (CTEs).

Using Subquery

A subquery with the IN operator can be used to specify which rows to delete. The subquery selects the primary key (or any unique column) of the rows that should be deleted, and the outer query deletes the rows that match these identifiers.

DELETE FROM your_table WHERE id IN ( SELECT id FROM your_table WHERE condition = true LIMIT 10 );

Replace your_table with the name of your table, id with the primary key or a unique identifying column, and adjust the condition = true part to fit your criteria.

Using Common Table Expressions (CTE)

With PostgreSQL 9.5 and later, you can use a CTE for deleting a limited number of rows in a more readable way.

WITH deleted AS ( DELETE FROM your_table WHERE condition = true RETURNING * ) SELECT * FROM deleted LIMIT 10;

In this example, replace your_table and condition = true as necessary. This approach deletes the rows matching the condition and returns them, but the actual deletion isn't limited by the LIMIT clause directly—instead, it's the result set of the SELECT statement that's limited. Although this might not directly limit the deletions in all scenarios, it's a useful technique for returning information about the rows that were deleted.

Important Notes

  • Use these methods with caution, especially in production environments, because deleting data is irreversible without proper backups.
  • Always test your queries in a development environment before applying them to your production database.
  • The choice between a subquery and CTE might depend on readability preferences, specific requirements of your application, or performance considerations.

Neither of these methods allows you to straightforwardly say 'delete exactly N rows matching these conditions,' but they provide the tools needed to achieve similar results with some creativity.

Was this content helpful?

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book
Start building today

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement.