I would like to keep a table of distinct places, with a restrictive UNIQUE (name, address) constraint.
When importing a new place, if it has a new (name, address) tuple I can insert it directly and get back the new id, if it fully matches an existing row (modulo administrative columns like created_at), I want to get back the existing id, but if it matches an existing (name, address) and other columns have a discrepancy, I want Postgres to complain about breaking the (name, address) uniqueness constraint.
Something like ON CONFLICT (name, address) REQUIRE (EXCLUDED.x, EXCLUDED.y) = (x, y) AND DO NOTHING.
It feels like it might be possible to contort a DO UPDATE SET clause that will either update zero columns or RAISE, but I haven't managed it; or if this is outside what ON CONFLICT can do, maybe triggers would be flexible enough.
Conceptually I just want a table that deduplicates inserts.
CREATE TABLE places (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
address VARCHAR NOT NULL,
x FLOAT NOT NULL,
y FLOAT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
UNIQUE(name, address)
);