Postgres Exists Query Syntax PostgreSQL Exists with SELECT Statement PostgreSQL Exists with INSERT Statement Postgres Exists with UPDATE Statement Postgres Exists with DELETE Statement PostgreSQL Exists Statement using pgAdmin
Postgres Exists Query Syntax
Here is the syntax for the PostgreSQL EXISTS statement: 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: 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: 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:
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: 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
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the Query Type the below query in the query editor: 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: 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)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: 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
