Search code examples
databasesubstringcockroachdbtable-index

Using a sub-string of the column name in creating index in CockroachDB

Moving from Oracle to CockroachDB, I'm using a index creating command as below:

CREATE INDEX ON "sales" (substring("a",0,19), substring("b",20,2), "c");

The error I get is:

invalid syntax: statement ignored: unimplemented at or near ")"
CREATE INDEX ON "sales" (substring("a",0,19), substring("b",20,2), "c");

I have also used: substr

but the error is the same.

--

Edit1: The error for the modified command

CREATE INDEX ON "sales" (substring("a",0,19));

will be the same.

Solution

  • You're trying to use a computed index - an index with keys that are more complex than column references. CockroachDB doesn't support computed indexes yet.

    There's an open issue for adding support for computed indexes:

    https://github.com/cockroachdb/cockroach/issues/9682