How to create an index on CASE expression in Postgres 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
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 ;