How to delete rows in PostgreSQL

DELETE FROM orders WHERE order_date < '2022-01-01'

To delete rows in a PostgreSQL database, you use the DELETE statement, specifying the target table and the condition that identifies which rows to remove. The basic syntax is DELETE FROM table_name WHERE condition;. The WHERE clause is crucial because it determines which rows will be deleted. If you omit the WHERE clause, all rows in the table will be deleted, so you must be cautious to include the appropriate condition.

You can also use various conditions in the WHERE clause to target specific rows. For example, you can delete rows based on equality, ranges, or even subqueries that match a specific criterion. For instance, DELETE FROM customers WHERE age < 18; would remove all rows from the customers table where the age column is less than 18.

Additionally, PostgreSQL offers the RETURNING clause in the DELETE statement, which allows you to return the deleted rows' data. This can be particularly useful for confirming what has been deleted or for maintaining a log of deleted information. For example, DELETE FROM orders WHERE order_date < '2022-01-01' RETURNING *; would delete all orders placed before January 1, 2022, and return the details of those deleted rows.