How to enforce only one NULL value per unique combination of some other columns?

[zymir]

here's the situation, I have a table A with like such

CREATE TABLE A
(
    id         UUID    NOT NULL PRIMARY KEY,
    B_id       bigint  NOT NULL,
    C_id       bigint  NOT NULL,
    some_value TIME,
    enabled    BOOLEAN NOT NULL,
CONSTRAINT A_B_id_fkey FOREIGN KEY (B_id) REFERENCES B (id),
CONSTRAINT A_C_id_fkey FOREIGN KEY (C_id) REFERENCES C (id)

);

And I want to have only one null value for some_value when enabled is false. Also, I want to ensure this null is for all foreign keys. So I can have x*y null values, but only one per couple of (x, y). I did something like this :

CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value
    ON A (B_id, C_id, (some_value IS NULL), (enabled IS FALSE))
    WHERE (some_value IS NULL);
CREATE UNIQUE INDEX CONCURRENTLY idx_A_some_value

ON A (B_id, C_id, (some_value IS NOT NULL), (enabled IS TRUE))

WHERE (some_value IS NOT NULL);

Does it seems correct? Is there a better way? Also, performance is important in my case. Thanks

Edit:

I reworked the conditions and did something much more simpler:

CREATE UNIQUE INDEX CONCURRENTLY idx_un_A
    ON A (B_id, C_type_id);
ALTER TABLE A

ADD CONSTRAINT some_value_null_not_enabled

CHECK ( (enabled IS FALSE AND some_value IS NULL) OR

(enabled IS TRUE AND some_value IS NOT NULL));

Is it better?

[emran]

I want to have only one null value for some_value when enabled is false.

A minimalist https://www.postgresql.org/docs/current/indexes-partial.html unique index can do that:

CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1)  -- constant
WHERE some_value IS NULL AND NOT enabled;

This index will hold at most one row.

I want to ensure this null is for all foreign keys. So I can have x*y null values, but only one per couple of (x, y).

A partial https://www.postgresql.org/docs/current/indexes-multicolumn.html unique index:

CREATE UNIQUE INDEX a_special_uni_idx ON a (a_id, b_id)
WHERE some_value IS NULL;

This allows each combination of (a_id, b_id) only once when some_value is null. Note that rows with NULL values in either a_id or b_id evade this restriction. See:

• https://stackoverflow.com/a/8289253/939860

I guess you want to allow at most one NULL value in some_value per (a_id, b_id).

I also guess your question is unclear.