PostgreSQL CASE expression

Like any other databases PostgreSQL also supports conditional expressions

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: