PostgreSQL Exists with Select, Insert, Update & Delete Example

What is Exists in PostgreSQL?

The PostgreSQL EXISTS operator tests whether a row(s) exists in a subquery. This means that the operator is used together with a subquery. The Exists operator is said to have been met when at least one row is found in the subquery. You can use this operation along with SELECT, UPDATE, INSERT, and DELETE statements.

In this PostgreSQL Exists Query tutorial, you will learn:

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:

PostgreSQL Exists with SELECT Statement

Price:

PostgreSQL Exists with SELECT Statement

Run the following statement:

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

This returns the following:

PostgreSQL Exists with SELECT Statement

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:

PostgreSQL Exists with INSERT Statement

Price:

PostgreSQL Exists with INSERT Statement

Price2:

PostgreSQL Exists with INSERT Statement

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:

PostgreSQL Exists with INSERT Statement

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);

Postgres Exists with UPDATE Statement

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:

Postgres Exists with DELETE Statement

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.

PostgreSQL Exists Statement using pgAdmin

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

PostgreSQL Exists Statement using pgAdmin

It should return the following:

PostgreSQL Exists Statement using pgAdmin

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.

Exists Statement in PostgreSQL using pgAdmin

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.

Exists Statement in PostgreSQL using pgAdmin

The Price table should now be as follows:

Exists Statement in PostgreSQL using pgAdmin

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.

Exists Statement in PostgreSQL using pgAdmin

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.

PostgreSQL Exists Statement using pgAdmin

The Price table should now be as follows:

PostgreSQL Exists Statement using pgAdmin

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.

PostgreSQL Exists Statement using pgAdmin

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.

PostgreSQL Exists Statement using pgAdmin

The Price table should now be as follows:

PostgreSQL Exists Statement using pgAdmin

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