PostgreSQL Delete | PostgreSQL Tutorials and Solutions

PostgreSQL Delete command is used to delete rows of a table. DELETE deletes rows that satisfy the WHERE clause from the specified table. If the WHERE clause is absent, the effect is to delete all rows in the table.

We must have the DELETE privilege on the table to delete from it.

PostgreSQL Delete Syntax:

 DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

PostgreSQL Delete Examples:

Lets create a table for below examples:

 CREATE TABLE students(SNO int PRIMARY KEY, SNAME varchar(50),DOB date, class int, gender varchar(6));

PostgreSQL Delete

1. Delete all students who are belongs to class 4.
Below query deletes all rows where class = 4.

delete from students where class=4;

 sno |  sname  |    dob     | class | gender
-----+---------+------------+-------+--------
 103 | Nancy   | 2008-02-13 |     5 | female
 105 | David   | 2012-05-08 |     3 | male
 104 | James   | 2007-02-13 |     5 | male
 102 | Cameron | 2011-12-23 |     5 | female

2. Delete all rows from table students.

Tip: Truncate is faster to delete large tables.

delete from students;

PostgreSQL Delete

3. Delete all employees except salary is less than 2000 from the table emp in PostgreSQL.

Before delete:

delete from emp where sal<2000;

PostgreSQL Delete