To delete data from a table PostgreSQL has a DELETE statement.
DELETE FROM table
The below rules need to be followed while using the DELETE statement:
- First, specify the table from which you want to delete data in the DELETE FROM clause.
- Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.
Let’s set up a sample database and table for the demonstration of DELETE statement.
- Create a database named “company” with the below command:
CREATE DATABASE company;
- Add a table of “employee” to show the company hierarchy into the database using the below command:
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE
- Now add some employee data to the table using the below command:
INSERT INTO employee (
(1, 'Sandeep', 'Jain', NULL),
(2, 'Abhishek ', 'Kelenia', 1),
(3, 'Harsh', 'Aggarwal', 1),
(4, 'Raju', 'Kumar', 2),
(5, 'Nikhil', 'Aggarwal', 2),
(6, 'Anshul', 'Aggarwal', 2),
(7, 'Virat', 'Kohli', 3),
(8, 'Rohit', 'Sharma', 3);
The value in the manager_id column represents the senior manager who the employee reports to. If it’s Null, he/she doesn’t report to anyone.
The overall hierarchy looks like the below image:
The current database tables look like below:
Here we will be deleting the employee data whose first name is “Raju”.
DELETE FROM employee
WHERE first_name = 'Raju';
Here we will delete multiple rows from the “employee” table. We will be deleting the data of the employee named “Abhishek Kelenia” and employees who work under him.
DELETE FROM employee
WHERE last_name = 'Kelenia';
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses
are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!