Open In App

PostgreSQL – DELETE

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


    Last Updated : 28 Aug, 2020
    Like Article
    Save Article
    Previous
    Next
    Share your thoughts in the comments
Similar Reads