Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "town" varchar(255) NOT NULL,
  "street" varchar(255) not null,
  "building" varchar(255) 
);

CREATE TABLE

ALTER TABLE "users"
ADD CONSTRAINT users_uni UNIQUE NULLS NOT DISTINCT ("town", "street", "building");

ALTER TABLE

---- example 2:
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 8);
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 8);

INSERT 0 1

ERROR:  duplicate key value violates unique constraint "users_uni"
DETAIL:  Key (town, street, building)=(t, s, 8) already exists.

---- example 1:
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 8);
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 9);
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', null);

INSERT 0 1

INSERT 0 1

INSERT 0 1

---- example 2:
bEGIN;
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 7);
Commit;
bEGIN;
INSERT INTO "users" ("town", "street", "building") VALUES ('t', 's', 7);
Commit;

BEGIN

INSERT 0 1

COMMIT

BEGIN

ERROR:  duplicate key value violates unique constraint "users_uni"
DETAIL:  Key (town, street, building)=(t, s, 7) already exists.