Search code examples
sqlderby

Create index on CASE expression in Derby

I' m trying to create an index on CASE expression, as follows:

CREATE UNIQUE INDEX symbol_unique_idx ON "USER" (CASE WHEN deleted = 0 THEN symbol ELSE NULL END);

and getting error:

Error: Syntax error: Encountered "CASE" at line 1, column 54.
SQLState:  42X01
ErrorCode: 30000

What am I doing wrong?

Solution

  • Derby doesn't support that syntax for CREATE INDEX.

    Here's the Derby documentation for CREATE INDEX: http://db.apache.org/derby/docs/10.14/ref/rrefsqlj20937.html

    Derby does support "generated columns":http://db.apache.org/derby/docs/10.14/ref/rrefsqljgenerationclause.html

    So perhaps you can change your table definition to include a column which is GENERATED AS (expression), with the desired expression to compute your data.

    And then you can include that column in your index.