How to create an index on CASE expression in Postgres

Question

I am trying to create an index on CASE expression, as follows

CREATE TABLE test(i INT, j INT);

CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j END);

Getting this error:

ERROR:  syntax error at or near "CASE"
LINE 1: CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j ...
                                               ^

********** Error **********

ERROR: syntax error at or near "CASE"
SQL state: 42601
Character: 40

What am I doing wrong?

Postgres 9.5.2

Answer 1

You need to add extra parentheses around the CASE expression:

CREATE UNIQUE INDEX test_index 
  ON test ((CASE WHEN i=1 THEN j END)) ;

As the docs state in CREATE INDEX:

The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.

---

Consider also using a filtered index, which is equivalent in terms of functionality but would be using less space, as it will be storing the j values only for rows with i = 1 and not the (possibly millions) or the rest NULL values:

CREATE UNIQUE INDEX test_index_2 
  ON test (j) WHERE i=1 ;