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

Question

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.

Answer 1

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

Answer 2

Cockroach-DB Create INDEX Sypnosis.

According to the automata that validates the string to create an index in a table, there is no possibility to make use of the substring function.