PostgreSQL multi-column unique constraint and NULL values I have a table like the following:

create table my_table (
    id   int8 not null,
    id_A int8 not null,
    id_B int8 not null,
    id_C int8 null,
    constraint pk_my_table primary key (id),
    constraint u_constrainte unique (id_A, id_B, id_C)
);


And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error:

INSERT INTO my_table VALUES (1, 1, 2, NULL);
INSERT INTO my_table VALUES (2, 1, 2, NULL);


But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error.

How can I guarantee my unique constraint even if id_C can be NULL in this case?
Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?
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:


Create unique constraint with null columns


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:


PostgreSQL UPSERT issue with NULL values


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:


Auto increment table column


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