PostgreSQL DELETE Statement

PostgreSQL DELETE Statement

The PostgreSQL DELETE statement is used to remove existing rows from a table. This statement is essential for deleting data that is no longer needed or for clearing out specific records based on conditions.

Syntax

DELETE FROM table_name
WHERE condition;

The DELETE statement has the following components:

  • table_name: The name of the table from which to delete rows.
  • condition: The condition to identify which rows to delete.

Example PostgreSQL DELETE Statement Queries

Let's look at some examples of PostgreSQL DELETE statement queries:

1. Basic DELETE Example

DELETE FROM employees
WHERE id = 1;

This query deletes the row from the employees table where the id is 1.

2. DELETE with Multiple Conditions

DELETE FROM employees
WHERE department_id = 1 AND salary > 50000;

This query deletes rows from the employees table where the department_id is 1 and the salary is greater than 50000.

3. DELETE All Rows

DELETE FROM employees;

This query deletes all rows from the employees table.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DELETE statement to remove data.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, salary, and department_id.

PostgreSQL DELETE

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000, 2);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jim', 'Brown', 'jim.brown@example.com', 55000, 1);

Here, we insert data into the employees table.

PostgreSQL DELETE

Step 3: Deleting Data from the Table

This step involves using the DELETE statement to remove data from the employees table.

Basic DELETE

DELETE FROM employees
WHERE id = 1;
PostgreSQL DELETE

DELETE with Multiple Conditions

DELETE FROM employees
WHERE department_id = 1 AND salary > 50000;
PostgreSQL DELETE

DELETE All Rows

DELETE FROM employees;
PostgreSQL DELETE

These queries demonstrate how to use the DELETE statement to remove data from the employees table, including deleting a single row, deleting based on multiple conditions, and deleting all rows.

Conclusion

The PostgreSQL DELETE statement is a fundamental tool for removing existing rows from a table. Understanding how to use the DELETE statement and its syntax is essential for effective data management and cleanup in PostgreSQL databases.