PostgreSQL Exists with Select, Insert, Update & Delete Example

Postgres Exists Query Syntax

Here is the syntax for the PostgreSQL EXISTS statement:

WHERE EXISTS (subquery);

The above syntax shows that the EXISTS operator takes in an argument, which is a subquery. The subquery is simply a SELECT statement that should begin with a SELECT * instead of a list of column names or expressions.

PostgreSQL Exists with SELECT Statement

Let’s see how to use a SELECT statement with the EXISTS operator. We have the following tables:

Book:

Price:

Run the following statement:

SELECT *
FROM Book
WHERE EXISTS (SELECT *
              FROM Price
              WHERE Book.id = Price.id);

This returns the following:

The above command should return all records in the Book table whose id matches the id of any records by the subquery. Only one id was matched. Hence, only one record was returned.

PostgreSQL Exists with INSERT Statement

We can use the EXISTS operator in an INSERT statement. We have the following 3 tables:

Book:

Price:

Price2:

We can then run the following statement:

INSERT INTO Price
SELECT id, price
FROM Price2
WHERE EXISTS (SELECT id
              FROM Book
              WHERE Book.id = Price2.id);

The Price table is now as follows:

The row with an id of 5 in the table named Price2 was matched. This record was then inserted into the Price table.

Postgres Exists with UPDATE Statement

We can use the EXISTS operator in an UPDATE statement.

Run the following query:

UPDATE Price
SET price = (SELECT price
              FROM Price2
              WHERE Price2.id = Price.id)
WHERE EXISTS (SELECT id, price
FROM Price2
WHERE Price2.id = Price.id);

We are updating the price column of the Price table. Our aim is to have the prices of items sharing an id to be the same. Only one row was matched, that is, 5.

However, since the prices are equal, that is, 205, no update was made. If there was a difference, an update would have been made.

Postgres Exists with DELETE Statement

A PostgreSQL DELETE statement can use the EXISTS operator. Here is an example:

DELETE FROM Price
WHERE EXISTS (SELECT *
              FROM Price2
              WHERE Price.id = Price2.id);

The Price table is now as follows:

The row with an id of 5 has been deleted.

PostgreSQL Exists Statement using pgAdmin

Now let’s see how these actions can be performed using pgAdmin:

How To Use Exists Query in PostgreSQL with SELECT Statement using pgAdmin

Below are the steps to use Exists query in PostgreSQL with SELECT statement using pgAdmin:

Step 1) Login to your pgAdmin account

Open pgAdmin and Login to your account using your credentials

Step 2) Create a Demo Database

1. From the navigation bar on the left- Click Databases.
2. Click Demo.

Step 3) Type the Query

Type the below query in the query editor:

SELECT *
FROM Book
WHERE EXISTS (SELECT *
              FROM Price
              WHERE Book.id = Price.id);

Step 4) Execute the Query

Click the Execute button

It should return the following:

With INSERT Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left- Click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

INSERT INTO Price
SELECT id, price
FROM Price2
WHERE EXISTS (SELECT id
              FROM Book
              WHERE Book.id = Price2.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

With UPDATE Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left- Click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

UPDATE Price
SET price = (SELECT price
              FROM Price2
              WHERE Price2.id = Price.id)
WHERE EXISTS (SELECT id, price
FROM Price2
WHERE Price2.id = Price.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

With DELETE Statement

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

1. From the navigation bar on the left- Click Databases.
2. Click Demo.

Step 3) Type the query in the query editor:

DELETE FROM Price
WHERE EXISTS (SELECT *
              FROM Price2
              WHERE Price.id = Price2.id);

Step 4) Click the Execute button.

The Price table should now be as follows:

Summary

• The EXISTS operator tests whether a row(s) exists in a subquery.
• It is used with a subquery and is said to have been met when the subquery returns at least one row.
• It is used together with SELECT, UPDATE, INSERT, and DELETE statements.

Download the Database used in this Tutorial