PostgreSQL – DELETE

To delete data from a table PostgreSQL has a DELETE statement.

Syntax: 
DELETE FROM table
WHERE condition;

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,
      manager_id INT,
      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 (
      employee_id,
      first_name,
      last_name,
      manager_id
  )
  VALUES
      (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:
  

  Example 1:
  Here we will be deleting the employee data whose first name is “Raju”.

  DELETE FROM employee
  WHERE first_name = 'Raju';

  Output:
  

  Example 2:
  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';

  Output: