PostgreSQL - DELETE

PostgreSQL - The DELETE Statement

The PostgreSQL DELETE statement is used to remove existing rows from a table. It’s crucial to use a WHERE clause to specify which rows to delete. If you omit the WHERE clause, all rows in the table will be deleted!

Syntax

DELETE FROM table_name
[ WHERE condition ];

Safety Tip: Before running a DELETE statement, it’s a best practice to first run a SELECT statement with the same WHERE clause to verify that you are targeting the correct rows. For example: SELECT * FROM table_name WHERE condition;

Example: Deleting Specific Rows

Let’s set up a products table to work with.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    price NUMERIC(10, 2)
);

INSERT INTO products (name, status, price) VALUES
('Laptop', 'active', 1200.00),
('Mouse', 'active', 25.00),
('Keyboard', 'discontinued', 75.00),
('Monitor', 'active', 300.00),
('Old Webcam', 'discontinued', 15.00);

Now, let’s delete a single product with an id of 2.

testdb=# DELETE FROM products WHERE id = 2;
DELETE 1

The message DELETE 1 indicates that one row was successfully deleted. Now let’s delete all products that have been ‘discontinued’.

testdb=# DELETE FROM products WHERE status = 'discontinued';
DELETE 2

The table now only contains the active products:

testdb=# SELECT * FROM products ORDER BY id;
 id |  name   | status |  price
----+---------+--------+---------
  1 | Laptop  | active | 1200.00
  4 | Monitor | active |  300.00
(2 rows)

Getting Data Back After Deleting with RETURNING

A powerful feature in PostgreSQL is the RETURNING clause, which allows the DELETE statement to return the values of the rows that were just deleted. This is extremely useful for logging, auditing, or knowing exactly what was removed without running a separate SELECT query.

Syntax

DELETE FROM table_name
WHERE condition
RETURNING column1, column2, ... | *;

Example

Let’s delete the ‘Monitor’ and see its id and name returned in the same command.

testdb=# DELETE FROM products WHERE name = 'Monitor' RETURNING id, name;
 id |  name
----+---------
  4 | Monitor
(1 row)

Deleting All Rows: DELETE vs. TRUNCATE

If your goal is to remove all rows from a table, you have two options: DELETE FROM table_name; or TRUNCATE table_name;. They achieve a similar result but have important differences.

Aspect	DELETE	TRUNCATE
Performance	Slower. Scans and deletes rows one by one.	Much faster. Deallocates the table’s data pages.
Triggers	Fires ON DELETE triggers for each row.	Does not fire ON DELETE triggers.
Transaction Log	Logs every single row deletion.	Minimal logging, making it faster.
Serial/Identity Columns	Does not reset the sequence.	Resets sequences to their starting value by default (RESTART IDENTITY).
WHERE Clause	Allowed.	Not allowed. It always removes all rows.

Example of TRUNCATE

To quickly empty the products table and reset its id sequence:

testdb=# TRUNCATE TABLE products RESTART IDENTITY;
TRUNCATE TABLE

Choose TRUNCATE when you need to quickly empty a large table during development or a data loading process. Use DELETE when you need to selectively remove rows, or if you have DELETE triggers that must be fired.