Make a DBMS-enforced multi-column "UNIQUE" constraint, with a "match all" wildcard

Question

Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour.

I have multiple columns. Now I wish to make sure each entry into the database has a unique column. Normally this would be a simple

ALTER TABLE test
ADD UNIQUE(X, Y)

However in our application "NULL" means not "no data" but rather "wildcard". (unless you think there's a better general purpose to specify this). What this means is that for unique constraint when one of the columns is not filled, it "matches everything", ie the following tables would violate unique constraints (in addition to normal constraints):

X    | Y
'a'  | 'B'
'a'  | null


X    | Y
'a'  | 'B'
null | 'B'

While this would be obviously "ok":

X    | Y
'a'  | 'B'
null | 'C'


X    | Y
'a'  | 'B'
'b'  | null

Can I put such a constraint on database level? Or do I have to guarantee this on server level?

ps: this is not the primary key, there is always a column id for primary keys. Also in this example I use strings, but actually the datatypes are different and can be anything.

Answer 1

I suggest you fix your design. Using NULL as a wildcard is not the best in my opinion. Splitting the table into 2 or 3 so the wildcard information is stored in separate tables would be better.

If you keep this design, it will need some complex exclusion constraints, besides the UNIQUE one:

CREATE TABLE test (
    test_id serial NOT NULL PRIMARY KEY,
    x int NULL,
    y int NULL,

-- unique constraint for non-null (x,y) values 
CONSTRAINT x_y_unique
    UNIQUE (x,y),

    -- when x IS NULL, do not allow a non-null x value (with same y)
    CONSTRAINT unique_with_wildcards_x_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE 1 END) WITH <>,
          y with =
        ),

    -- do not allow 2 NULL x values (with same y)
    CONSTRAINT unique_with_wildcards_x_one_null
        EXCLUDE USING GIST
        ( (CASE WHEN x IS NULL THEN 0 ELSE NULL END) WITH =,
          y with =
        ),

    -- when y IS NULL, do not allow a non-null y value (with same x)   
    CONSTRAINT unique_with_wildcards_y_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE 1 END) WITH <>
        ),

    -- do not allow 2 NULL y values (with same x)
    CONSTRAINT unique_with_wildcards_y_one_null
        EXCLUDE USING GIST
        ( x with =,
          (CASE WHEN y IS NULL THEN 0 ELSE NULL END) WITH =
        )
) ;

Tested in dbfiddle.uk.

Notice that the (NULL, NULL) combination is allowed. If you want this to be treated as a super-wildcard, (so if it's in the table, no other rows would be allowed), you'll need some more exclusion constraints.