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);

Answer 2

The CockroachDB already supports creating indices by computed expressions but still doesn't support unique indices by nullable columns.

So an alternative workaround for partial uniqueness by only a subset of records non-nullable by one of the columns can be to coalesce to an alternative unique group of columns like this:

create unique index if not exists some_table_partial_id_semi_unique_index
    on some_table (coalesce(partial_id, a_column || '_' || b_column));

-- double-check that the index is really used:
explain select * from some_table where partial_id = '13';