7

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

14

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 ;
  • I'd +1 more for the filtered index suggestion. – Colin 't Hart Jun 22 '17 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.