How to ensure uniqueness with NULL values in cockroach DB

Question

I have a table of accounts which generates unique keys with a sequence. It is used to generate unique identities for accounts and account/user pairs.

I tried the following from pgsql:

CREATE INDEX ON accounts (account_id, user_id) WHERE user_id IS NULL;

and

CREATE UNIQUE INDEX ON accounts (account_id, COALESCE(user_id, ''));

But both cases are not supported in cockroach.

Is there another way of ensuring uniqueness with NULL values?

I also have a different UNIQUE index on user_id to allow unique IDs to be generated for users without an account.

Answer 1

You may be able to do something like create a computed column and then use that in an index:

alter table accounts add column user_null int null as (coalesce(user_id, 0));
create unique index on accounts (account_id, user_null);