Doc: how to guarentee case insensitive uniqueness for a string

[mattcrdb]

Matthew Vardi (mattcrdb) commented:

A customer brought up an issue when migrating over to CRDB.

How can he guarantee that an email string column prevents case insensitive duplicates.

One solution is to create a computed column using the lower() function and then applying the unique constraint using alter table.

ALTER TABLE users ADD COLUMN clean_email STRING AS (lower(email)) STORED;
ALTER TABLE users ADD CONSTRAINT unique_clean_email UNIQUE (clean_email).

I have confirmed this works as intended, however, might not be the best solution at scale.

Another solution suggested by @justinj is to use collate. I have not looked into how this works.

Postgres has a citext data type that allows for this functionality, from their documentation: The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

Jira Issue: DOC-321

[jseldess]

@bdarnell, @awoods187, @justinj, should this be on the SQL roadmap somewhere?

[bdarnell]

See cockroachdb/cockroach#22463, cockroachdb/cockroach#9682, and #1471.

Note that the definition of "case insensitivity" differs based on language (Turkish is the most notable one that is incompatible with English rules).

[jseldess]

@rmloveland, assigning to 20.1 milestone, since there's likely enough to handle for 19.2 already. But feel free to change.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB docs!

[exalate-issue-sync]

Richard Loveland (rmloveland) commented:
Ryan Kuo assigning to you since it’s SQL Queries / Migrations (your areas per this doc)

There’s also an interesting internal discussion on collation and migrating from SQL Server that seems related to this