On 21 May 2015 at 16:27, Peter Geoghegan <pg@heroku.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@2ndQuadrant.com', 10.0);
2. INSERT INTO customers VALUES ('sriggs', 'simon@2ndQuadrant.com', 10.0);
3. INSERT INTO customers VALUES ('sriggs2', 'simon@2ndQuadrant.com', 10.0);
4. INSERT INTO customers VALUES ('sriggs', 'simon.riggs@2ndQuadrant.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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services