PostgreSQL DELETE Statement: Removing Data from a Table

Summary: in this tutorial, you’ll learn how to use the PostgreSQL DELETE statement to delete one or more rows from a table.

PostgreSQL DELETE statement

The PostgreSQL DELETE statement is a powerful tool that permanently removes one or more rows from a table.

Here’s the syntax of the DELETE statement:

DELETE FROM table_name
WHERE condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the table name for which you want to delete rows in the DELETE FROM clause.
• Second, provide a condition in the WHERE clause to filter which rows to delete.

Since the WHERE clause is optional, you can omit it in the DELETE statement. In this case, the DELETE statement will delete all rows from the table.

If no row meets the condition in the WHERE clause, the DELETE statement will not delete any rows from the table.

PostgreSQL returns a command tag indicating the number of rows deleted by the DELETE statement:

DELETE count;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the count is zero, the DELETE statement does not delete any row from the table.

Setting up a sample table

We’ll delete rows from the inventories table:

CREATE TABLE inventories (
  name VARCHAR(255),
  brand VARCHAR(50),
  quantity INT,
  price DECIMAL(19, 2)
);

INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 14 Pro', 'Apple', 10, 999.99),
       ('Galaxy S23 Ultra', 'Samsung', 15, 1199.99),
       ('Pixel 7 Pro', 'Google', 8, 899.99),
       ('Xperia 1 IV', 'Sony', 7, 1299.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Deleting one row from a table

The following statement uses the DELETE statement to delete one row from the inventories table:

DELETE FROM inventories
WHERE name = 'iPhone 14 Pro';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

DELETE 1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the condition in the WHERE clause matches one row. Therefore, the DELETE statement deleted only that row.

The output indicates that the DELETE statement deleted one row successfully.

Deleting all rows from a table

The following statement uses the DELETE statement to delete all rows from the inventories table:

DELETE FROM inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This DELETE statement doesn’t use a WHERE clause, so it removes all remaining rows from the inventories table. This could be useful when you want to clear a table completely, for example, before re-populating it with new data.

Output:

DELETE 3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output shows that the DELETE statement removed three rows from the inventories table.

Returning deleted rows

The DELETE statement has an optional RETURNING clause that returns the deleted rows.

DELETE FROM table_name
WHERE condition
RETURNING column1, column2, ...;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, specify a list of comma-separated columns from the deleted row you want to return. If you want to return all the columns of the deleted row, you can use the asterisk (*) as follows:

DELETE FROM table_name
WHERE condition
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, first, insert a new row into the inventories table:

INSERT INTO inventories (name, brand, quantity, price)
VALUES ('iPhone 15 Pro', 'Apple', 5, 1299.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, delete the 'iPhone 15 Pro' and return the deleted row:

DELETE FROM inventories
WHERE name = 'iPhone 15 Pro'
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

     name      | brand | quantity |  price
---------------+-------+----------+---------
 iPhone 15 Pro | Apple |        5 | 1299.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

• Use the PostgreSQL DELETE statement to delete one or more rows from a table.
• Use the RETURNING clause to return the deleted rows to the client.