PostgreSQL: Delete Data in a Table

In PostgreSQL, use the DELETE statement to delete one or more rows in the table. It only deletes the data from the table and not the table structure.

DELETE FROM <table_name>
[WHERE <condition<]
RETURNING * | <output_expression< AS <output_name<;

In the above syntax,

• Specify the name of the table where you want to delete the data after the DELETE FROM keyword.
• The WHERE clause is optional which limits the delete operation specific to the specified condition. If you do not specify the WHERE clause, Postgres will delete all the rows of a table.
• The RETURNING clause is optional which will return a list of all deleted rows or values of the specified column.

After the DELETE statement is executed successfully, PostgreSQL will give the message as DELETE count where the count is the number of rows deleted by the DELETE statement.

Consider the following employee table:

Now, let's remove data from the employee table whose emp_id = 5.

DELETE FROM employee
WHERE emp_id = 5;

The above query will display the following result in pgAdmin:

The above output DELETE 1 shows 1 row is deleted from the employee table.

The DELETE statement can remove multiple records from the employee table. For example, now we will remove rows where emp_id = 3 and 4 and will return the deleted row after executing the DELETE statement.

DELETE FROM employee
WHERE emp_id IN (3, 4);

The above query will display the following result in pgAdmin:

The DELETE 2 indicates that it has deleted 2 rows from the table.

RETURNING Clause with DELETE Statement

The RETURNING clause returns the deleted rows or column values. If you specify RETURNING * then it will return all the deleted rows and if you specify RETURNING column_name then it will return values of the specified columns. You can specify multiple columns separated with a comma.

The following DELETE statement returns all the deleted rows:

DELETE FROM employee
WHERE emp_id = 2
RETURNING *;

The following DELETE statement returns specified columns of deleted rows:

DELETE FROM employee
WHERE emp_id IN (5, 7)
RETURNING emp_id, first_name, last_name;

Delete All Data from a Table

The DELETE statement can be used to delete all rows from a table if you omit the WHERE clause, as shown below.

DELETE FROM employee;

The above has deleted all the rows from the employee table. Let's verify it by running the SELECT query.