In this tutorial, we will learn about the DELETE statement in PostgreSQL with examples. The delete statement is used to delete the table records. Let’s see the DELETE statement in depth.
Also Read: PostgreSQL – A Complete Introduction, History, and Features
Introduction to DELETE in PostgreSQL
The DELETE statement in SQL is used to delete single or multiple records from the table. Almost all RDBMS have the DELETE keyword for deleting the records.
Note that, the DELETE statement can be used to delete a single row or multiple rows, even all rows of the table.
Syntax of DELETE Query in PostgreSQL
Let’s see the syntax of the DELETE statement in PostgreSQL.
DELETE FROM tableName
WHERE condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)While using the DELETE statement in any relational database, you have to remember the following things:
- Specify the correct table name from which you have to delete the rows.
- Specify the correct condition to delete the particular rows. If the condition is somehow incorrect, you may lose the records that you didn’t want to.
- If you don’t specify the WHERE clause, all records from the table will be deleted.
To delete all rows from the table, use the TRUNCATE statement. It provides faster mechanism than the DELETE to remove all rows.
Postgresql provides us with the optional RETURNING clause which returns the deleted rows.
Syntax of DELETE Query with RETURNING clause
The following syntax shows the usage of the RETURNING clause.
DELETE FROM tableName
WHERE condition
RETURNING (columnList | *);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In the above syntax, if you specify the asterisk symbol(*), the query will display all column names.
Examples of DELETE in PostgreSQL
To demonstrate the DELETE statement, let’s create a table and insert some data into it.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10, 2)
);
INSERT INTO employees (name, department, salary)
VALUES
('John Doe', 'HR', 5000.00),
('Jane Smith', 'IT', 6000.00),
('Mark Johnson', 'Sales', 7000.00),
('Sarah Williams', 'HR', 5500.00),
('Michael Brown', 'Finance', 6500.00),
('Emily Davis', 'IT', 5500.00),
('Alex Thompson', 'Sales', 7500.00),
('Jessica Wilson', 'IT', 5200.00),
('David Miller', 'Finance', 6200.00),
('Michelle Anderson', 'HR', 5300.00);
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Let’s query the SELECT statement to see if the data is correctly inserted.
SELECT * FROM employees;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
Perfect!
Delete a Single Row in PostgreSQL
To target a single row, use the WHERE clause having the unique column name in the condition, e.g. id, UUID etc.
Here, I will delete the employees having ids 1 and 7.
DELETE FROM employees
WHERE id=1;
DELETE FROM employees
WHERE id=7;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see in the output, two records having ids 1 and 7 are deleted from the table.
Deleting Multiple Rows in PostgreSQL
To delete multiple rows, you can specify any column name in the WHERE condition which is not unique.
Here, I want to delete all the records of employees having salaries less than 6000.
DELETE FROM employees
WHERE salary<=6000;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, PostgreSQL will target all the rows having salaries less than 6000 and deletes them. Let’s see the output.
Output:
As you can see, only rows having a salary greater than 6000 have remained.
Return Deleted Rows in PostgreSQL
Let’s see how we can use the RETURNING statement to see which rows are deleted. Before executing the next query, I will reinsert all the data into the table.
DELETE FROM employees
WHERE id<=5
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here, we are deleting all the rows having IDs less than 5. Let’s see its output.
Output:
As you can see, all the deleted rows have been displayed.
Deleting All Rows in PostgreSQL
The DELETE statement is also used to delete all rows from the table. Let’s see.
DELETE FROM employees;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, all the remaining records have been deleted.
However, as I said earlier, the TRUNCATE statement is better than the DELETE statement if you want to delete all records from the table. Use the DELETE statement only when you want to target specific rows to delete.
Conclusion
In this tutorial, we have learned about the DELETE statement, how to use it and when to use it. It is pretty straightforward to learn and understand all the things mentioned in this tutorial. If you are not sure about the WHERE condition before deleting the rows, you may use the SELECT statement first with the same WHERE condition to check which rows are being targeted by the WHERE condition.
Reference
PostgreSQL official documentation on the DELETE statement.