Cannot index a composite type created out of one or more indexable types #120531
Description
In Postgres the following is possible:
demodb=# create type fakecitex as (a text);
CREATE TYPE
CREATE TABLE IF NOT EXISTS emails (
email_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
email fakecitex NOT NULL UNIQUE
);
Notice the unique index on the fakecitex composite type.
In CockroachDB however adding the UNIQUE index returns an error:
root@localhost:26257/system/defaultdb> create type fakecitex as (a text);
CREATE TYPE
Time: 53ms total (execution 52ms / network 1ms)
root@localhost:26257/system/defaultdb> CREATE TABLE IF NOT EXISTS emails (
-> email_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
-> email fakecitex NOT NULL UNIQUE,
-> );
ERROR: unimplemented: column email is of type fakecitex and thus is not indexable
SQLSTATE: 0A000
HINT: You have attempted to use a feature that is not yet implemented.
See: https://go.crdb.dev/issue-v/35730/v23.2
To make this simple we could also only allow unique indexing on composite type if the composite type consists of a single indexable type. However, Postgres does support unique indexes on composite type made up of multiple types.
Example:
create type fakecitex as (a text, b int);
CREATE TABLE IF NOT EXISTS emails (
email_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
email fakecitex NOT NULL UNIQUE
);
demodb=# INSERT INTO emails (email) VALUES (('ab', 2));
INSERT 0 1
demodb=# SELECT * FROM emails;
email_id | email
----------+--------
1 | (ab,2)
(1 row)
demodb=# INSERT INTO emails (email) VALUES (('ab', 2));
ERROR: duplicate key value violates unique constraint "emails_email_key"
DETAIL: Key (email)=((ab,2)) already exists.
demodb=# INSERT INTO emails (email) VALUES (('ab', 3));
INSERT 0 1
demodb=# INSERT INTO emails (email) VALUES (('abc', 3));
INSERT 0 1
demodb=# SELECT * FROM emails;
email_id | email
----------+---------
1 | (ab,2)
3 | (ab,3)
4 | (abc,3)
(3 rows)
Jira issue: CRDB-36734
Epic CRDB-60817