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?
Other Common PostgreSQL Questions (and Answers)
- How do you manage Postgres replication lag?
- How can I limit the number of rows updated in a PostgreSQL query?
- How does sharding work in PostgreSQL?
- How do you use the PARTITION OVER clause in PostgreSQL?
- What are PostgreSQL replication slots and how do they work?
- How can you partition an existing table in PostgreSQL?
- How do you partition a table by multiple columns in PostgreSQL?
- How do you check the replication status in PostgreSQL?
- What are the scaling limits of PostgreSQL?
- How do you scale Azure PostgreSQL?
- How do you use the limit clause in PostgreSQL to get the top N rows of a query result?
- How can I improve delete performance in PostgreSQL?
Free System Design on AWS E-Book
Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.
Switch & save up to 80%
Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement. Instantly experience up to a 25X boost in performance and 80% reduction in cost