Postgres Delete Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re using PostgreSQL to store and manage data, there may be times when you need to remove certain records from a table. The DELETE statement makes it easy to remove records that match whatever conditions you specify. In this article, we’ll explain how to perform a Postgres delete statement against data in a table and provide some examples of how this statement is used.

Prerequisite

In order to follow along with the SQL examples shown in this tutorial, you’ll need to have PostgreSQL installed and configured on your machine.

What is the Postgres DELETE Statement?

The Postgres DELETE statement allows us to delete targeted rows within a table. Shown below is the basic form of the DELETE statement:

1
2
DELETE FROM <table_name>
WHERE <optional_condition>

Let’s look at this syntax more closely:

  • First, we provide the name of the table from which we want to delete the data using the clause DELETE FROM.

  • We then specify the exact row(s) to delete with the help of the WHERE clause. Although the WHERE clause is optional, it serves to limit which records get deleted. If you omit the WHERE clause in your DELETE statement, everything in the table will be deleted.

Create Sample Database

In this section, we’ll create the sample dataset that we will be using throughout this article.

We start by creating our database:

1
CREATE DATABASE toydb;

Next, we connect to our newly-created database using the command \c toydb;.

We can then create our sample table using the following statement:

1
        CREATE TABLE toy (toy_id INT PRIMARY KEY, toy_name VARCHAR(100), product_no VARCHAR (20), qty INT);

Finally, we insert several records into the table:

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO toy (toy_id, toy_name, product_no, qty)
VALUES
   (1,'Buzz Light Year','T011',21),
   (2,'Woody','T012',32),
   (3,'Garfield','T013',54),
   (4,'Ultra Man','T014',12),
   (5,'Potato Man','T015',89),
   (6,'Wolverine','T016',21),
   (7,'Storm','T017',32),
   (8,'Kimpossible','T018',54),
   (9,'The Mask','T019',12),
   (10,'Lara Croft','T020',89);

Our table should now look something like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
 toy_id |    toy_name     | product_no | qty
--------+-----------------+------------+-----
      1 | Buzz Light Year | T011       |  21
      2 | Woody           | T012       |  32
      3 | Garfield        | T013       |  54
      4 | Ultra Man       | T014       |  12
      5 | Potato Man      | T015       |  89
      6 | Wolverine       | T016       |  21
      7 | Storm           | T017       |  32
      8 | Kimpossible     | T018       |  54
      9 | The Mask        | T019       |  12
     10 | Lara Croft      | T020       |  89
(10 rows)

Postgres DELETE Statement Example

Now that we’ve created a sample table containing some records, we’ll show some examples of how to perform the DELETE operation using this sample dataset.

DELETE statement with WHERE clause

First, let’s take a look at the DELETE statement with the WHERE helper clause.

As we mentioned earlier, the basic form of the DELETE statement with a WHERE clause looks like the following:

1
2
DELETE FROM toy
WHERE product_no = 'T012';

This query will return a ‘1’, which means we deleted one record– the row with a ‘product_no’ field that contains a value of ‘T012’.

If no records in the table match the specified conditions in the WHERE clause, a ‘0’ will be returned, indicating that no records were deleted.

The result should look something like this:

1
2
3
4
5
6
7
8
9
10
11
12
 toy_id |    toy_name     | product_no | qty
--------+-----------------+------------+-----
      1 | Buzz Light Year | T011       |  21
      3 | Garfield        | T013       |  54
      4 | Ultra Man       | T014       |  12
      5 | Potato Man      | T015       |  89
      6 | Wolverine       | T016       |  21
      7 | Storm           | T017       |  32
      8 | Kimpossible     | T018       |  54
      9 | The Mask        | T019       |  12
     10 | Lara Croft      | T020       |  89
(9 rows)

Our next example is a bit more complex:

1
2
DELETE FROM toy
WHERE qty <= 21;

The query shown above uses the ‘<=’ comparison operator, which will search for records where the value of the ‘qty’ field is les than or equal to 21.

The result should look like this:

1
2
3
4
5
6
7
8
 toy_id |  toy_name   | product_no | qty
--------+-------------+------------+-----
      3 | Garfield    | T013       |  54
      5 | Potato Man  | T015       |  89
      7 | Storm       | T017       |  32
      8 | Kimpossible | T018       |  54
     10 | Lara Croft  | T020       |  89
(5 rows)

DELETE all rows

If we want to delete all the records in a table, we can use the following query:

1
DELETE FROM toy RETURNING *;

Notice that there’s no WHERE clause in the statement shown above. This tells Postgres to delete every record in the table since there are no defined conditions to be satisfied.

Our results will look something like this:

1
2
3
4
5
6
7
8
9
10
11
 toy_id |  toy_name   | product_no | qty
--------+-------------+------------+-----
      3 | Garfield    | T013       |  54
      5 | Potato Man  | T015       |  89
      7 | Storm       | T017       |  32
      8 | Kimpossible | T018       |  54
     10 | Lara Croft  | T020       |  89
(5 rows)


DELETE 5

We used the RETURNING clause in our statement to return the number of rows deleted. In this case, you can see that we have deleted five rows.

Conclusion

When you need to remove data from a PostgreSQL table, it’s important to proceed with care. The DELETE operation is permanent, so you don’t want to make a mistake and delete all the records in your table. In this article, we showed you how to use the Postgres DELETE statement to remove records that meet specific criteria from a PostgreSQL table. With our detailed instructions and examples, you’ll be able to use the DELETE statement in your own PostgreSQL environment.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.