DECODE function in Oracle is an extension to CASE expression and have the following syntax.

DECODE(expr, val1, res1, val2, res2, .., default)

DECODE compares expr to each search value val1, val2 one by one and goes on.

• If expr is equal to a search value, then Oracle Database returns the corresponding result.
• If no match is found, then Oracle returns default.
• If default is omitted, then Oracle returns null.

PostgreSQL database does not have DECODE or a similar function, so you must convert DECODE to CASE expression in PostgreSQL. Have a look at the following examples on how DECODE is converted to CASE expression.

Scenario 1: If expr is equal to a search value, then Oracle Database returns the corresponding result.

Oracle:

SQL> COL res FORMAT A5;
SQL> SELECT decode('A', 'R', 'One', 'A', 'Two', 'None') res FROM DUAL;

RES
---
Two

PostgreSQL: Converting DECODE as CASE

--DECODE function does not exist in PostgreSQL
postgres=# SELECT decode('A', 'R', 'One', 'A', 'Two', 'None') res;
ERROR:  function decode(unknown, unknown, unknown, unknown, unknown, unknown) does not exist
LINE 1: SELECT decode('A', 'R', 'One', 'A', 'Two', 'None') res;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


postgres=# SELECT CASE 'A' 
postgres-#          WHEN 'R' THEN 'One'
postgres-#          WHEN 'A' THEN 'Two'
postgres-#          ELSE 'None'
postgres-#     END res;
 res 
-----
 Two
(1 row)

Scenario 2: If no match is found, then Oracle returns default value specified.

Oracle:

SQL> SELECT decode('AX', 'R', 'One', 'A', 'Two', 'None') res FROM DUAL;

RES
----
None

PostgreSQL:

postgres=# SELECT CASE 'AX' 
postgres-#          WHEN 'R' THEN 'One'
postgres-#          WHEN 'A' THEN 'Two'
postgres-#          ELSE 'None'
postgres-#     END res;
 res  
------
 None
(1 row)

Scenario 3: If default is omitted, then Oracle returns NULL.

Oracle:

SQL> SELECT decode('AX', 'R', 'One', 'A', 'Two') res FROM DUAL;

RES
---

PostgreSQL: Specify ELSE NULL or can omit. In either scenario CASE returns NULL.

postgres=# SELECT CASE 'AX' 
postgres-#          WHEN 'R' THEN 'One'
postgres-#          WHEN 'A' THEN 'Two'
postgres-#          ELSE NULL
postgres-#     END res;
 res 
-----
 
(1 row)

Converting DECODE to CASE though is an option but is time consuming if has to be done manually. Moreover sometimes converting DECODE could be confusing and challenging with nested DECODE functions and with varying data types. Since Oracle database is rich in doing implicit type casting, DECODE never throws error having arguments of varied types, whereas the chances to get type cast issues while converting to CASE is more in PostgreSQL.

Let’s see what does this mean by type cast issue with CASE expression in PostgreSQL.

Oracle:

SQL> SELECT decode('A', 'B', 'B1', 'C', 'C1', 3) res FROM DUAL;

RES
-----
3

PostgreSQL:

postgres=# SELECT CASE 'A' 
postgres-#          WHEN 'B' THEN 'B1'
postgres-#          WHEN 'C' THEN 'C1'
postgres-#          ELSE 3
postgres-#     END res;
ERROR:  invalid input syntax for type integer: "B1"
LINE 2:          WHEN 'B' THEN 'B1'

What’s the correct solution? Always the data type for the value in ELSE must match the data type of other values in WHEN clause. Check the correct conversion for above CASE expression.

postgres=# SELECT CASE 'A' 
postgres-#          WHEN 'B' THEN 'B1'
postgres-#          WHEN 'C' THEN 'C1'
postgres-#          ELSE 3::TEXT
postgres-#     END res;
 res 
-----
 3
(1 row)

Introducing DECODE wrapper function in PostgreSQL

To simplify migration of DECODE and expedite code migration, you can create a wrapper function.

CREATE OR REPLACE FUNCTION DECODE(VARIADIC ANYARRAY)
RETURNS ANYELEMENT 
AS 
$BODY$
DECLARE
  v_len INTEGER;
  v_params ALIAS FOR $1;
  v_idx INTEGER;

BEGIN

  v_len = COALESCE(array_length(v_params,1),0);
  
  IF v_len < 3 THEN
    RAISE USING ERRCODE = '00938', MESSAGE = 'not enough arguments for function';
  END IF;

  v_idx := 2;
  LOOP
     EXIT WHEN v_idx > (v_len-1);
     IF v_params[1] = v_params[v_idx] THEN 
         RETURN v_params[v_idx+1];
     END IF; 
     
     IF v_params[1] IS NULL AND v_params[v_idx] IS NULL THEN
         RETURN v_params[v_idx+1];
     END IF;
     
     v_idx := v_idx + 2;
  END LOOP;

  IF v_idx = v_len THEN
      RETURN v_params[v_idx];
  END IF;

  RETURN NULL;

END;
$BODY$
    LANGUAGE 'plpgsql'
    COST 100 STABLE;

Testing the wrapper function

Let’s test the wrapper function created above with the same parameters in both the environment.

postgres=# SELECT decode('AX', 'R', 'One', 'A', 'Two', 'None') res;
ERROR:  could not determine polymorphic type because input has type unknown


postgres=# SELECT decode('A'::TEXT, 'R', 'One', 'A', 'Two', 'None') res;
 res 
-----
 Two
(1 row)

postgres=# SELECT decode('AX'::TEXT, 'R', 'One', 'A', 'Two', 'None') res;
 res  
------
 None
(1 row)

postgres=# SELECT decode('AX'::TEXT, 'R', 'One', 'A', 'Two') res;
 res 
-----
 
(1 row)

postgres=# SELECT decode('A'::TEXT, 'B', 'B1', 'C', 'C1', 3::TEXT) res;
 res 
-----
 3
(1 row)

Limitation:

• All input parameters to wrapper DECODE should be of same type.