PostgreSQL Tutorial: DELETE

Summary: in this tutorial, you will learn how to use the PostgreSQL DELETE statement to delete data from a table.

Table of Contents

Introduction to PostgreSQL DELETE statement

The PostgreSQL DELETE statement allows you to delete one or more rows from a table.

The following shows basic syntax of the DELETE statement:

DELETE FROM table_name
WHERE condition;

In this syntax:

• First, specify the name of the table from which you want to delete data after the DELETE FROM keywords.
• Second, use a condition in the WHERE clause to specify which rows from the table to delete.

The WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

The DELETE statement returns the number of rows deleted. It returns zero if the DELETE statement did not delete any row.

To return the deleted row(s) to the client, you use the RETURNING clause as follows:

DELETE FROM table_name
WHERE condition
RETURNING (select_list | *)

The asterisk (*) allows you to return all columns of the deleted row from the table_name.

To return specific columns, you specify them after the RETURNING keyword.

Note that the DELETE statement only removes data from a table. It doesn’t modify the structure of the table. If you want to change the structure of a table such as removing a column, you should use the ALTER TABLE statement.

PostgreSQL DELETE statement examples

Let’s set up a sample table for the demonstration.

The following statements create a new table called links and insert some sample data:

DROP TABLE IF EXISTS links;

CREATE TABLE links (
    id serial PRIMARY KEY,
    url varchar(255) NOT NULL,
    name varchar(255) NOT NULL,
    description varchar(255),
    rel varchar(10),
    last_update date DEFAULT now()
);

INSERT INTO  
   links 
VALUES 
   ('1', 'https://www.rockdata.net/tutorial/', 'PostgreSQL Tutorial', 'Learn PostgreSQL fast and easy', 'follow', '2013-06-02'),
   ('2', 'http://www.oreilly.com', 'O''Reilly Media', 'O''Reilly Media', 'nofollow', '2013-06-02'),
   ('3', 'http://www.google.com', 'Google', 'Google', 'nofollow', '2013-06-02'),
   ('4', 'http://www.yahoo.com', 'Yahoo', 'Yahoo', 'nofollow', '2013-06-02'),
   ('5', 'http://www.bing.com', 'Bing', 'Bing', 'nofollow', '2013-06-02'),
   ('6', 'http://www.facebook.com', 'Facebook', 'Facebook', 'nofollow', '2013-06-01'),
   ('7', 'https://www.tumblr.com/', 'Tumblr', 'Tumblr', 'nofollow', '2013-06-02'),
   ('8', 'http://www.postgresql.org', 'PostgreSQL', 'PostgreSQL', 'nofollow', '2013-06-02');

Here are the contents of the links table:

SELECT * FROM links;

1) Using PostgreSQL DELETE to delete one row from the table

The following statement uses the DELETE statement to delete one row with the id 8 from the links table:

DELETE FROM links
WHERE id = 8;

The statement returns 1 indicated that one row has been deleted:

DELETE 1

The following statement uses the DELETE statement to delete the row with id 10:

DELETE FROM links
WHERE id = 10;

Since the row with id 10 does not exist, the statement returns 0:

DELETE 0

2) Using PostgreSQL DELETE to delete a row and return the deleted row

The following statement deletes the row with id 7 and returns the deleted row to the client:

DELETE FROM links
WHERE id = 7
RETURNING *;

PostgreSQL returns the following deleted row:

3) Using PostgreSQL DELETE to delete multiple rows from the table

The following statement deletes two rows from the links table and return the values in the id column of deleted rows:

DELETE FROM links
WHERE id IN (6,5)
RETURNING *;

Output:

4) Using PostgreSQL DELETE to delete all rows from the table

The following statement uses the DELETE statement without a WHERE clause to delete all rows from the links table:

DELETE FROM links;

The links table now is empty.

Summary

• Use the DELETE FROM statement to delete one or more rows from a table.
• Use the WHERE clause to specify which rows to be deleted.
• Use the RETURNING clause to return the deleted rows.