Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- auxiliary function
-- requires additional module btree_gist
CREATE EXTENSION btree_gist;

CREATE OR REPLACE FUNCTION texthash_int8range(text)
  RETURNS int8range
  LANGUAGE sql IMMUTABLE PARALLEL SAFE
RETURN int8range(hashtextextended($1, 0), hashtextextended($1, 0), '[]');

CREATE EXTENSION

CREATE FUNCTION

CREATE TABLE users (
  id serial PRIMARY KEY
, town text NOT NULL
, street text NOT NULL
, building text
, CONSTRAINT address_uni UNIQUE NULLS NOT DISTINCT (town, street, building)
, CONSTRAINT address_with_null_building EXCLUDE USING gist(hash_record_extended((town, street), 0) WITH =, texthash_int8range(building) WITH &&)
);

CREATE TABLE

-- all OK
INSERT INTO users (town, street, building) VALUES ('t1', 's', 8);
INSERT INTO users (town, street, building) VALUES ('t1', 's', 9);

INSERT INTO users (town, street, building) VALUES ('t2', 's', null);

INSERT 0 1

INSERT 0 1

INSERT 0 1

-- violates unique AND exclusion constraint
INSERT INTO users (town, street, building) VALUES ('t2', 's', null);

ERROR:  duplicate key value violates unique constraint "address_uni"
DETAIL:  Key (town, street, building)=(t2, s, null) already exists.

-- violates unique AND exclusion constraint
INSERT INTO users (town, street, building) VALUES ('t1', 's', 9);

ERROR:  duplicate key value violates unique constraint "address_uni"
DETAIL:  Key (town, street, building)=(t1, s, 9) already exists.

-- can't insert with null building
-- since the same (town, street) with notnull building exists
INSERT INTO users (town, street, building) VALUES ('t1', 's', null);

ERROR:  conflicting key value violates exclusion constraint "address_with_null_building"
DETAIL:  Key (hash_record_extended(ROW(town, street), 0::bigint), texthash_int8range(building))=(8691446948776307684, (,)) conflicts with existing key (hash_record_extended(ROW(town, street), 0::bigint), texthash_int8range(building))=(8691446948776307684, [1993718048345588864,1993718048345588865)).

-- can't insert with notnull building
-- since the same (town, street) with null building exists
INSERT INTO users (town, street, building) VALUES ('t2', 's', 8);

ERROR:  conflicting key value violates exclusion constraint "address_with_null_building"
DETAIL:  Key (hash_record_extended(ROW(town, street), 0::bigint), texthash_int8range(building))=(3825496747261263164, [1993718048345588864,1993718048345588865)) conflicts with existing key (hash_record_extended(ROW(town, street), 0::bigint), texthash_int8range(building))=(3825496747261263164, (,)).