EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL IF Statement
Secondary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions

PostgreSQL IF Statement

By Payal UdhaniPayal Udhani

PostgreSQL IF Statement

Introduction to PostgreSQL IF Statement

Using the queries that will give control of the database and allow the user to manipulate it effectively and strongly are provided in any SQL or database language. Some statements help the user have better control over the queries and help in decision-making based on PostgreSQL conditions; these statements are called the control statements. One of the most crucial and powerful out of all of them is the if statement. This statement allows us to execute certain code only when some condition is fulfilled. In this article, we will learn about the general syntax of if statement in PostgreSQL and understand its practical usage with the help of some examples.

Syntax

IF condition THEN
-- code or statements to be executed END IF;

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Explanation: The condition is the boolean expression that evaluates to true or false. This condition can be any expression ranging from variable comparison to query result or isNull statement or exists statement that returns a boolean value; if this condition evaluates to true, then the code or statements you want to execute are specified after THEN keyword is executed. The body of the if block starts from the THEN keyword till the END IF statement. If the condition doesn’t evaluate to true, then the statements in the if block is simply skipped. This is the syntax of if statement in psql.

How does PostgreSQL IF Statement work?

Usage: We can use this if statement in functions, stored procedures, the sequence of statements inside DO statement or with loops such as simple LOOP statement, for loop, while loop, etc. using expressions with declared variables, numbers, string comparisons, query results, and any functions that return boolean value like isnull, exists and many other.

Examples to Implement PostgreSQL IF Statement

Let us consider a simple example where we will declare one variable named age. If the age is greater than 18, then we will raise a message in the form of a notice saying that “he/she is eligible to vote”. Let us see how we can perform this execution with the help of do statement.

Example #1

Code:

DO $$ DECLARE
age integer := 23;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote!';
END IF;
END $$;

Output:

Example 1

Explanation: The DO statement specifies that Postgres needs to execute the following statements below it. Then comes the declaration part, where we declare our variable named age and initialize it to 23 integer value. Then we begin or execution part where we will place our if statement in which we will check whether age is greater than 18; if so, the raise a notice saying, “You are eligible to vote!”.

Example #2

Now, let us initialize our variable to a value less than 19, for example, 15 and see that is the output. Then the output is seen somewhat like the following :

Code:

DO $$ DECLARE
age integer := 15;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote!';
END IF;
END $$;

Output:

PostgreSQL IF Statement2

Explanation: No notice is raised, and the statements in the if block is simply skipped for execution.

Example #3

Let us write a function and try to call them by passing the age variable instead of statically declaring and initializing in the above example:

Code:

CREATE OR REPLACE FUNCTION iseligible(int) RETURNS void AS $$ DECLARE
age int:=$1;
BEGIN
IF age > 18 THEN
RAISE NOTICE 'You are eligible to vote as your age is %!', age; END IF;
END;
$$ LANGUAGE plpgsql;

Output:

PostgreSQL IF Statement3

Explanation: This will create a function named iseligible. We can call this function whenever we want to pass the value of the age for which you want to check eligibility. The output after copying and pasting the above statements on the psql command prompt terminal is as follows.

Example #4

Now, we want to check the eligibility for a 21-year-old guy; then we can call our function in the following way:

Code:

SELECT iseligible(21);

Output:

eligibility function

Explanation: This displays the notice along with our message, which means the condition inside the if-statement evaluated to true and if the block’s body was executed.

Example #5

Now, let us try with a value less than 19, take 15. Then our calling statement will be:

Code:

SELECT iseligible(15);

Output :

calling statement

Example #6

Let us take one example where we will take a for loop and use if statement in it. We have to retrieve all the even numbers between the two numbers that are passed to our function and print them. Our function will then be like this:

Code:

CREATE OR REPLACE FUNCTION displayEvenNumbers(int,int) RETURNS void AS $$ DECLARE
first  int; last int:=$2; BEGIN
IF $1%2=0
THEN first=$1;
ELSE first=$1+1;
END IF;
FOR sampleCounter IN first..last BY 2 LOOP
RAISE NOTICE 'Even numbers : %', sampleCounter; END LOOP;
END;
$$ LANGUAGE plpgsql;

Output:

loop and use if statement

The above function would give the following output if the function created successfully.

Explanation: Here, the first parameter will be starting number and the second will be the ending number between which we want even numbers. We will first check whether the starting number first is an even or odd number by using the modulus operator and if statement. If the first parameter is even, we will assign that value to the “first” variable; else, we will add one to the first parameter value and then assign that value to the “first” variable. Then we will use a for loop ranging from the “first” variable to the “last” variable to whom the value of the second parameter is assigned, and the step value will be 2 as all even numbers will have a difference of two between consecutive even values.

Example #7

Now we can call the displayevennumbers function to get the even numbers between 15 and 22 using the following query statement.

Code:

SELECT displayevennumbers(15,22);

Output:

displayevennumbers

Conclusion

If the statement is the simplest form of conditional statement that can be used to determine condition based on which the statements can be executed when the condition will evaluate to true. If the statement can be used in, do statements, functions or stored procedures, and loops and query statements for any expression mentioned in the condition that evaluates to a boolean result.

Recommended Articles

This is a guide to PostgreSQL IF Statement. Here we discuss an introduction to PostgreSQL IF Statement along with syntax, How does it work and examples with code and output. You can also go through our other related articles to learn more –

  1. PostgreSQL LIMIT
  2. PostgreSQL Alias
  3. PostgreSQL ROLLUP
  4. PostgreSQL Functions | How to Works?
  5. PostgreSQL log | Top 9 Parameters
  6. PostgreSQL cluster | Examples
  7. Guide to SQL While Loop
Popular Course in this category
PostgreSQL Course (2 Courses, 1 Project)
  2 Online Courses |  1 Hands-on Project |  7+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more