The if Statement in PostgreSQL

An if statement evaluates conditions by returning a true or false value. We can use it to execute queries based on a condition.

This tutorial will teach us how to write if statement queries and understand how conditional statements help us when developing back-end applications.

Write Queries With if Statements to Execute Different Operations in PostgreSQL

1. Use the following command to log in to the PostgreSQL server. Enter the password in the prompt that returns and press enter.

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

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

   postgres=#
   

3. Use the following command to create a new database:

   postgres=# create database if_statement_db;
   CREATE DATABASE
   

4. To shift from the postgres database to if_statement_db, use the command provided below and notice that we connect 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=#
   

5. Create a table named phone with fields id, phone_name, phone_color, and phone_price. Use the data definition query 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
   

6. Insert some records into the phone table. These records will help us in executing the conditional if statement queries.

   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
   

7. Create a file on your machine and name it data.sql or any name you like. Write your if statement query to be executed against the phone table inside 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 there are records present, these will be deleted from the table.

   The query inserts new records if the table does not have any.

8. Use the following command to execute the data.sql file. Since our table contains some values, we should expect the delete operation to be performed because the if condition will evaluate to true.

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

   A query to retrieve all the values in the database returns zero records.

   if_statement_db=# select * from phone;
   

   Output:

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