Re: INSERT ... ON CONFLICT DO UPDATE with _any_ constraint

On 21 May 2015 at 16:27, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> Try and convince me.
>

(There is no "try")

CREATE TABLE customers
(username TEXT PRIMARY KEY
,email TEXT UNIQUE
,billing NUMERIC(11,2)
);

1. INSERT INTO customers VALUES ('sriggs', 'simon(at)2ndQuadrant(dot)com', 10.0);
2. INSERT INTO customers VALUES ('sriggs', 'simon(at)2ndQuadrant(dot)com', 10.0);
3. INSERT INTO customers VALUES ('sriggs2', 'simon(at)2ndQuadrant(dot)com', 10.0);
4. INSERT INTO customers VALUES ('sriggs', 'simon(dot)riggs(at)2ndQuadrant(dot)com',
10.0);

How should we choose to handle the above data?

We might choose these rules:
If no constraints violate, accept insert
If both constraints violate, treat it as a repeat record and just set the
billing to the new value.
If first constraint violates but not second, treat it as an email address
update AND increment the billing, if any
If second constraint violates, reject the row since we only allow one
userid per person

With 2 constraints we have 4 permutations, i.e. O(2^N) permutations. If we
are claiming to handle multiple constraints, I don't think we should just
assume that they can all use the same UPDATE. I might point out that the
MERGE syntax allowed us to handle that fully, but you'll probably scream.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services