PostgreSQL: deduplicate inserts, but preserve uniqueness for a column subset

16 Views Asked by At

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)
);
0

There are 0 best solutions below