You can do that in pure SQL. Create a partial unique index in addition to the one you have:
CREATE UNIQUE INDEX ab_c_null_idx ON my_table (id_A, id_B) WHERE id_C IS NULL;
This way you can enter for (id_A, id_B, id_C)
in your table:
(1, 2, 1)
(1, 2, 2)
(1, 2, NULL)
But none of these a second time.
Or use two partial UNIQUE
indexes and no complete index (or constraint). The best solution depends on the details of your requirements. Compare:
While this is elegant and efficient for a single nullable column in the UNIQUE
index, it gets out of hand quickly for more than one. Discussing this - and how to use UPSERT with partial indexes:
Asides
No use for mixed case identifiers without double quotes in PostgreSQL.
You might consider a serial
column as primary key or an IDENTITY
column in Postgres 10 or later. Related:
So:
CREATE TABLE my_table (
my_table_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY -- for pg 10+
-- my_table_id bigserial PRIMARY KEY -- for pg 9.6 or older
, id_a int8 NOT NULL
, id_b int8 NOT NULL
, id_c int8
, CONSTRAINT u_constraint UNIQUE (id_a, id_b, id_c)
);
If you don't expect more than 2 billion rows (> 2147483647) over the lifetime of your table (including waste and deleted rows), consider integer
(4 bytes) instead of bigint
(8 bytes).
In other words, you want values in the column subset
to be unique among rows where the column type
is 'true'.
A partial unique index will do that:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';
Data type does matter. If the column type
is boolean
(likely should be), you can simplify:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type;
This way you can even make combinations with NULL
unique, which is not possible otherwise. See:
Best Answer
First, for constraint number 2, nothing special is needed. A
UNIQUE
constraint ignores - and accepts -NULL
values so a simple unique constraint on(w,x,y)
will work just fine enforcing that.For constraint number 1, I don't think there's a way with DDL in version 5.6 so your best option is probably the trigger solution by @stefan.
If you can upgrade to 5.7, you can use a
GENERATED
column and a unique index on it: