On Thu, May 21, 2015 at 1:50 PM, Simon Riggs wrote:
> (There is no "try")
>
> CREATE TABLE customers
> (username TEXT PRIMARY KEY
> ,email TEXT UNIQUE
> ,billing NUMERIC(11,2)
> );
>
> 1. INSERT INTO customers VALUES ('sriggs', 'si...@2ndquadrant.com', 10.0);
> 2. INSERT INTO customers VALUES ('s
On 21 May 2015 21:15, "Simon Riggs" wrote:
> I would like to see this happen now before we get hit with usage
questions similar to OP's. If both requests cannot happen now, if we can at
least agree a path for future enhancement we can refer people to what will
happen in later releases when they as
On 21 May 2015 at 16:27, Peter Geoghegan 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', 'si...@2ndquadrant.com', 10.0);
2. INSERT INTO customers VALU
On Thu, May 21, 2015 at 1:27 PM, Peter Geoghegan wrote:
>> As the patch author I hope and expect that you will listen to this and
>> consider how you will resolve these problems, just as any of us has done
>> when they are the patch author, even after commit. I would like to see this
>> happen now
On Thu, May 21, 2015 at 1:15 PM, Simon Riggs wrote:
> OK, let me summarise. First, thanks for putting time into this feature; we
> all wish to see it work and work well.
You're welcome.
> The current ON CONFLICT syntax requires us to specify one-and-only-one
> conflict_target/conflict_action pai
On 21 May 2015 at 15:44, Peter Geoghegan wrote:
> > Please look at the $SUBJECT of this thread. We're here now.
>
> What do you want me to do about it? I've said that I think that what
> you say about not mandating the inference clause in the parser could
> be okay. If you want to change it, obv
On Thu, May 21, 2015 at 11:55 AM, Simon Riggs wrote:
>> > It seems strange to force the user to think about constraint handling
>> > and
>> > then not offer them any choices once they have done the thinking.
>>
>> What if both constraints are violated? Won't the update end up in trouble?
>
>
> Gre
* Simon Riggs (si...@2ndquadrant.com) wrote:
> On 21 May 2015 at 14:25, Peter Geoghegan wrote:
> > > If the update is the same no matter which constraint is violated, why
> > would
> > > I need to specify the constraint? We're forcing the developer to make an
> > > arbitrary choice between two con
On 21 May 2015 at 14:25, Peter Geoghegan wrote:
>
> > If I have two constraints and I think about it, I would want to be able
> to
> > specify this...
> >
> > INSERT
> > ON CONFLICT (col1) DO UPDATE... (handle it one way)
> > ON CONFLICT (col2) DO UPDATE... (handle it 2nd way)
> >
> > but I canno
On Thu, May 21, 2015 at 9:51 AM, Simon Riggs wrote:
> No not all, but we can evaluate the constraints one at a time in a
> consistent order.
We do so currently. Now, you point out that that might not be the most
useful ordering, and as it happens I agree. But changing that ordering
to not just be
On 20 May 2015 at 05:49, Geoff Winkless wrote:
> On 19 May 2015 at 21:57, Simon Riggs wrote:
>
>> It's not clear to me how a single INSERT could cause two or more UPDATEs.
>>
>
>
> CREATE TABLE mytable (
> c1 int NOT NULL,
> c2 int NOT NULL,
> PRIMARY KEY (c1),
> UNIQUE (c2)
>
> );
>
On 19 May 2015 at 19:59, Peter Geoghegan wrote:
> On Tue, May 19, 2015 at 2:28 PM, Simon Riggs
> wrote:
> > On 19 May 2015 at 17:10, Peter Geoghegan wrote:
> >>
> >> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs
> >> wrote:
> >> > We should allow DO UPDATE to exclude a constraint and apply a
>
On 19 May 2015 at 21:57, Simon Riggs wrote:
> It's not clear to me how a single INSERT could cause two or more UPDATEs.
>
CREATE TABLE mytable (
c1 int NOT NULL,
c2 int NOT NULL,
PRIMARY KEY (c1),
UNIQUE (c2)
);
INSERT INTO mytable (c1, c2) (10, 20);
INSERT INTO mytable (c1, c2) (
On Tue, May 19, 2015 at 5:03 PM, Tom Lane wrote:
> Peter Geoghegan writes:
>> I think I agree with you, though: We should change things so that the
>> relcache gives indexes in something like the ordering that you
>> outline, rather than in the current arbitrary (though consistent) OID
>> order.
Peter Geoghegan writes:
> I think I agree with you, though: We should change things so that the
> relcache gives indexes in something like the ordering that you
> outline, rather than in the current arbitrary (though consistent) OID
> order.
I'm fairly sure that there are aspects of the code that
On Tue, May 19, 2015 at 2:28 PM, Simon Riggs wrote:
> On 19 May 2015 at 17:10, Peter Geoghegan wrote:
>>
>> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs
>> wrote:
>> > We should allow DO UPDATE to exclude a constraint and apply a
>> > deterministic
>> > order to the constraints. 1. PK if it exis
On 19 May 2015 at 17:10, Peter Geoghegan wrote:
> On Tue, May 19, 2015 at 1:57 PM, Simon Riggs
> wrote:
> > We should allow DO UPDATE to exclude a constraint and apply a
> deterministic
> > order to the constraints. 1. PK if it exists. 2. Replica Identity, when
> not
> > PK, 3. UNIQUE constraint
On Tue, May 19, 2015 at 1:57 PM, Simon Riggs wrote:
> We should allow DO UPDATE to exclude a constraint and apply a deterministic
> order to the constraints. 1. PK if it exists. 2. Replica Identity, when not
> PK, 3. UNIQUE constraints in name order, like triggers, so users can define
> a default
On 19 May 2015 at 16:36, Geoff Winkless wrote:
> On 19 May 2015 at 21:12, Peter Geoghegan wrote:
>
>> It's trivial to modify Postgres to not require that a specific unique
>> index be inferred, so that you can omit the inference specification
>> for DO UPDATE just as you can for DO NOTHING. That
On Tue, May 19, 2015 at 1:36 PM, Geoff Winkless wrote:
> On 19 May 2015 at 21:12, Peter Geoghegan wrote:
>>
>> It's trivial to modify Postgres to not require that a specific unique
>> index be inferred, so that you can omit the inference specification
>> for DO UPDATE just as you can for DO NOTHI
On 19 May 2015 at 21:12, Peter Geoghegan wrote:
> It's trivial to modify Postgres to not require that a specific unique
> index be inferred, so that you can omit the inference specification
> for DO UPDATE just as you can for DO NOTHING. That would make it work
> in a similar way to MySQL; whatev
On Tue, May 19, 2015 at 12:57 PM, Geoff Winkless wrote:
> Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
> that a conflict_target clause is required but doesn't explain why.
Yes, for ON CONFLICT DO UPDATE, it is mandatory.
> It _does_ make clear that multiple UPDATEs t
On 19 May 2015 at 20:11, Simon Riggs wrote:
> I'm sure we'll be asked these questions many times.
>
> Can you comment on whether the docs are sufficiently detailed to explain
> this answer?
>
Well http://www.postgresql.org/docs/devel/static/sql-insert.html explains
that a conflict_target clause
On 19 May 2015 at 11:49, Geoff Winkless wrote:
> On 19 May 2015 at 16:32, I wrote:
>
>> In the event that the INSERT triggers a constraint that the UPDATE fails
>> to resolve, it will still fail in exactly the same way that running the ON
>> CONFLICT on a specific constraint would fail, so it's n
On 19 May 2015 at 16:32, I wrote:
> In the event that the INSERT triggers a constraint that the UPDATE fails
> to resolve, it will still fail in exactly the same way that running the ON
> CONFLICT on a specific constraint would fail, so it's not like you gain any
> extra value from specifying the
I finally got around to running some UPSERT tests on the development build,
which is very exciting for me :)
I'm not sure if I missed the point with this (probably...): I'm unclear on
the reason why DO UPDATE requires explicitly specifying the constraint
while DO NOTHING does not.
If it's a featu
26 matches
Mail list logo
