CODEWITHSUNDEEP
sqloracle-databasepostgresql
vmb
vmb

Reputation: 3028

Decode equivalent in postgres

There is no equivalent to the Oracle's DECODE() Function In Postgres. Is there anyone who wrote decode as a Function?

Upvotes: 33

Views: 121286

Answers (4)

Vladimir Pankov
Vladimir Pankov

Reputation: 457

In addition to the main answer, "END CASE" does not apply if the case expression is used in the select, just use "END".

Oracle:

select decode(1+1, 2, 'yes', 'no') from dual

Postgres:

select case 1+1 when 2 then 'yes' else 'no' end

Upvotes: 5

Fernando Meneses Gomes
Fernando Meneses Gomes

Reputation: 867

You can combine NULLIF with COALESCE:

SELECT COALESCE(NULLIF(value, 0), newValue) FROM table;

Font: Coalesce for zero instead of null

Upvotes: 4

mvp
mvp

Reputation: 116078

If you are used to Oracle specific functions, you might want to install PostgreSQL extension orafce.

Among other Oracle specific functions, orafce also implements DECODE - one that you are looking for.

If you are running on Ubuntu, you will simply need to install package postgresql-9.1-orafce to make orafce available in your PostgreSQL server.

Upvotes: 4

Mitch Wheat
Mitch Wheat

Reputation: 300529

There is an equivalent. It's called a CASE statement.

There are two forms of CASE:

Simple CASE:

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Searched CASE:

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

CASE statements are easier to read; I prefer these over decode() in Oracle.

Upvotes: 35

Related Questions