PostgreSQL Conditional expression

PostgreSQL supports below conditional expressions

  1. CASE
  2. COALESCE
  3. NULLIF
  4. GREATEST
  5. LEAST

Note :- Although COALESCE, GREATEST, and LEAST are syntactically similar to functions, they are not ordinary functions

CASE

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages

Basically there are two categories , one with expression, one without

1. CASE without expression

CASE WHEN condition THEN result
[WHEN …]
[ELSE result]
END

Examples:-

postgres=# select empno,deptno from emp;
 empno | deptno 
-------+--------
    10 |      5
    11 |      5
    12 |      5
    13 |      1
    14 |      1
    15 |      1
    16 |      2
    17 |      2
    18 |      2
(9 rows)

postgres=# select empno,deptno,
           case 
           when deptno = 1 then 'one'
           when deptno = 2 then 'two'
           when deptno = 3 then 'three'
           when deptno = 4 then 'four'
           when deptno = 5 then 'five'
           else 'none'
           end as deptnoscript
           from emp;
 empno | deptno | deptnoscript 
-------+--------+--------------
    10 |      5 | five
    11 |      5 | five
    12 |      5 | five
    13 |      1 | one
    14 |      1 | one
    15 |      1 | one
    16 |      2 | two
    17 |      2 | two
    18 |      2 | two
(9 rows)

postgres=# select empno,deptno,
            case 
            when deptno between 1 and 3 then 'less than 3'
            when deptno > 3 then 'greater than 3'
            else 'none'
            end as deptnoscript
            from emp;
 empno | deptno |  deptnoscript  
-------+--------+----------------
    10 |      5 | greater than 3
    11 |      5 | greater than 3
    12 |      5 | greater than 3
    13 |      1 | less than 3
    14 |      1 | less than 3
    15 |      1 | less than 3
    16 |      2 | less than 3
    17 |      2 | less than 3
    18 |      2 | less than 3
(9 rows)

1. CASE with expression

CASE expression
WHEN value THEN result
[WHEN …]
[ELSE result]
END

Examples:-

postgres=# select case (select 'abc') 
           when 'abc' then 'abc'
           else 'somethingelse'
           end as colname;
 colname 
---------
 abc
(1 row)

Note:- (select 'abc') is the expression here. You can use sql query with tables also, but make sure it returns only 1 record at a time, see below 

postgres=# select case (select empno::varchar from emp) 
                        when 'abc' then 'abc'
                        else 'somethingelse'
                        end as colname;
ERROR:  more than one row returned by a subquery used as an expression

postgres=# select case (select empno::varchar from emp limit 1) 
                  when 'abc' then 'abc'
                  else 'somethingelse'
                  end as colname;
    colname    
---------------
 somethingelse
(1 row)

Note that PostgreSQL does not support DECODE sql command which is available in Oracle ( Please refer Oracle DECODE statement ).

Alternate of DECODE in PostgreSQL is CASE statement

Oracle decode Query

select decode(‘abc’,’abc’,’match’,’no-match’) from dual;

PostgreSQL case Query

select case ‘abc’
when ‘abc’ then ‘match’
else ‘no-match’
end;

COALESCE

COALESCE returns first not null value, at least one argument is mandatory

select COALESCE (null, ‘value’, null) will return ‘value’

Examples:-

postgres=# select COALESCE(null, 'value',null);
 coalesce 
----------
 value
(1 row)


postgres=# select COALESCE(null, null,null);
 coalesce 
----------
 
(1 row)
 
                       
postgres=# select COALESCE(1);
 coalesce 
----------
        1
(1 row)


postgres=# select COALESCE((select 1),null);                                                            coalesce 
----------
        1
(1 row)


postgres=# select COALESCE((select empno from emp where empno = 0),null);
 coalesce 
----------
         
(1 row)


postgres=# select COALESCE((select empno from emp where empno = 130),null);
 coalesce 
----------
         
(1 row)


postgres=# select COALESCE((select empno from emp where empno = 10),null);
 coalesce 
----------
       10
(1 row)

NULLIF

Syntax :- NULLIF(value1, value2)

The NULLIF function returns a null value if value1 equals value2

Examples :-

postgres=# select NULLIF(1,1);
 nullif 
--------
       
(1 row)

postgres=# select NULLIF(1,null);
 nullif 
--------
      1
(1 row)

postgres=# select NULLIF(null,1);
 nullif 
--------
       
(1 row)

                     
postgres=# select NULLIF((select 1),(select 2));
 nullif 
--------
      1
(1 row)

postgres=# select NULLIF((select 1),(select 1));
 nullif 
--------
       
(1 row)

postgres=# select NULLIF((select empno from emp where empno = 10),(select 10));
 nullif 
--------
       
(1 row)

postgres=# select NULLIF((select empno from emp where empno = 10),(select 11));
 nullif 
--------
     10
(1 row)

GREATEST and LEAST

GREATEST(value [, …])

LEAST(value [, …])

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of
expressions

Examples:-

postgres=# select GREATEST(1,2,3);
 greatest 
----------
        3
(1 row)

postgres=# select LEAST(1,2,3);
 least 
-------
     1
(1 row)
postgres=# select GREATEST('a','b','c');
 greatest 
----------
 c
(1 row)

postgres=# select LEAST('a','b','c');
 least 
-------
 a
(1 row)


postgres=# select GREATEST('a','b',3);
ERROR:  invalid input syntax for type integer: "a"
LINE 1: select GREATEST('a','b',3);
                        ^
Error :- due to incompatible data type. Data type of all values must be similar


postgres=# select LEAST('a','b',3);
ERROR:  invalid input syntax for type integer: "a"
LINE 1: select LEAST('a','b',3);
             
Error :- due to incompatible data type. Data type of all values must be similar

postgres=# select LEAST('a','b',3::varchar);
 least 
-------
 3
(1 row)

postgres=# select LEAST('a','b',3::varchar,null);
 least 
-------
 3
(1 row)

postgres=# select GREATEST('a','b',3::varchar,null);
 greatest 
----------
 b
(1 row)

Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some
other databases make them return NULL if any argument is NULL, rather than only when all are
NULL.