The predicate is silently ignored when using ALTER TABLE ... ADD CONSTRAINT ... UNIQUE ... WHERE .... For example:
CREATE TABLE tab (
a INT,
b STRING
);
ALTER TABLE tab ADD CONSTRAINT key_a_b UNIQUE (a, b);
ALTER TABLE tab ADD CONSTRAINT key_b_partial UNIQUE (b) WHERE a = 1;
SHOW CREATE TABLE tab;
table_name | create_statement
-------------+-------------------------------------------
tab | CREATE TABLE public.tab (
| a INT8 NULL,
| b STRING NULL,
| UNIQUE INDEX key_a_b (a ASC, b ASC),
| UNIQUE INDEX key_b_partial (b ASC),
| FAMILY "primary" (a, b, rowid)
| )
This is not the behavior when the constraint is included in the CREATE TABLE statement. For example:
CREATE TABLE tab (
a INT,
b STRING,
CONSTRAINT key_a_b UNIQUE (a, b),
CONSTRAINT key_b_partial UNIQUE (b) WHERE a = 1
);
SHOW CREATE TABLE tab;
table_name | create_statement
-------------+-------------------------------------------------------------
tab | CREATE TABLE public.tab (
| a INT8 NULL,
| b STRING NULL,
| UNIQUE INDEX key_a_b (a ASC, b ASC),
| UNIQUE INDEX key_b_partial (b ASC) WHERE a = 1:::INT8,
| FAMILY "primary" (a, b, rowid)
| )
In order to add a partial unique constraint after creating a table, the workaround is to use CREATE INDEX:
CREATE TABLE tab (
a INT,
b STRING
);
CREATE UNIQUE INDEX key_a_b ON tab (a, b);
CREATE UNIQUE INDEX key_b_partial ON tab (b) WHERE a = 1;
SHOW CREATE TABLE tab;
table_name | create_statement
-------------+-------------------------------------------------------------
tab | CREATE TABLE public.tab (
| a INT8 NULL,
| b STRING NULL,
| UNIQUE INDEX key_a_b (a ASC, b ASC),
| UNIQUE INDEX key_b_partial (b ASC) WHERE a = 1:::INT8,
| FAMILY "primary" (a, b, rowid)
This bug has been around since v20.2 (the above terminal output is from v20.2, but it's still an issue on master, too).
Note that including a predicate on a constraint results in a syntax error in Postgres. I think we should continue to allow using a predicate with the constraint (it's useful for UNIQUE WITHOUT INDEX, etc), but we should not silently ignore it.
The predicate is silently ignored when using
ALTER TABLE ... ADD CONSTRAINT ... UNIQUE ... WHERE .... For example:This is not the behavior when the constraint is included in the CREATE TABLE statement. For example:
In order to add a partial unique constraint after creating a table, the workaround is to use CREATE INDEX:
This bug has been around since v20.2 (the above terminal output is from v20.2, but it's still an issue on master, too).
Note that including a predicate on a constraint results in a syntax error in Postgres. I think we should continue to allow using a predicate with the constraint (it's useful for
UNIQUE WITHOUT INDEX, etc), but we should not silently ignore it.