JIYIK CN >

ifStatement evaluates a condition by returning true or false value. We can use it to perform condition based queries.

This article will teach you how to write ifconditional statements and understand how conditional statements can help you when developing backend applications.

Use the statement to write queries in PostgreSQL ifto perform different operations

Use the following command to log in to the PostgreSQL server. Enter the password in the returned prompt and press Enter.

david@david-HP-ProBook-6470b:~/Documents/work/upwork/jhinku-tutorials$ psql -U postgres
Password for user postgres:

The default user postgres has a database called postgres, we need to create our database, which we will use to test our if statement query.

postgres=#

Create a new database using the following command:

postgres=# create database if_statement_db;
CREATE DATABASE

To move from the postgres database to if_statement_db, use the command provided below and note that we are connected to the new database as user postgres.

postgres=# \c if_statement_db;
You are now connected to database "if_statement_db" as user "postgres".
if_statement_db=#

Create a table called phone with the fields id, phone_name, phone_color, and phone_price. Define a query using the data provided below.

if_statement_db=# create table phone(
if_statement_db(# id SERIAL UNIQUE NOT NULL,
if_statement_db(# phone_name varchar(50),
if_statement_db(# phone_type varchar(50),
if_statement_db(# phone_price integer,
if_statement_db(# PRIMARY KEY(id));
CREATE TABLE

Insert some records in the phone table. These records will help us to perform the conditional if statement query.

if_statement_db=# insert into phone(phone_name, phone_type, phone_price)
if_statement_db-# values('Sumsung A7', 'Refurbished',600);
INSERT 0 1
if_statement_db=# insert into phone(phone_name, phone_type, phone_price)
if_statement_db=# values('Motorola', 'new',800);
INSERT 0 1
if_statement_db=# insert into phone(phone_name, phone_type, phone_price)
if_statement_db=# values('Iphone 10', 'new',700);
INSERT 0 1

Create a file on your machine and name it data.sql or anything you like. Write the if statement query to be executed against the phone table in this file.

Example (data.sql):

DO
$do$
BEGIN
    IF EXISTS(SELECT * FROM phone) THEN
        DELETE FROM phone;
    ELSE
        INSERT into phone(phone_name, phone_type, phone_price)
        VALUES('Sumsung A7', 'Refurbished',600);

        INSERT into phone(phone_name, phone_type, phone_price)
        VALUES('Motorola', 'new',800);

        INSERT into phone(phone_name, phone_type, phone_price)
        VALUES('Iphone 10', 'new',700);
    END IF;
END
$do$

Our query will check if any records exist in the table. If records exist, those records will be deleted from the table.

If the table does not have any records, then the query inserts a new record.

Execute the data.sql file using the following command. Since our table contains some values, we should expect the delete operation to be performed as the if condition will evaluate to true.

if_statement_db=# \i /home/david/Documents/work/upwork/jhinku-tutorials/data.sql;
DO

A query that retrieves all values ​​in the database returns zero records.

if_statement_db=# select * from phone;

Output:

 id | phone_name | phone_type | phone_price
----+------------+------------+-------------
(0 rows)