On 7 May 2015 at 18:37, Andres Freund and...@anarazel.de wrote:
I don't see a problem at all, with one exception: If we want the AS to
be optional like in a bunch of other places, we have to either promote
VALUES to a reserved keyword, only accept unreserved keywords, or play
precedence
On 6 May 2015 at 22:30, Heikki Linnakangas hlinn...@iki.fi wrote:
On 05/07/2015 12:01 AM, Andres Freund wrote:
On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
I'll see about fixing that. It's not just a matter of creating another
alias
for the same rel, I'm afraid: foo.t is
On Thu, May 7, 2015 at 10:37 AM, Andres Freund and...@anarazel.de wrote:
I don't see a problem at all, with one exception: If we want the AS to
be optional like in a bunch of other places, we have to either promote
VALUES to a reserved keyword, only accept unreserved keywords, or play
On 05/07/2015 12:01 AM, Andres Freund wrote:
How about
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
From an implementation pov that'd be simple ;)
I did this, because as you say it's simple to implement, and it resolves
On 05/07/2015 12:01 AM, Andres Freund wrote:
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
If we don't allow AS whatever, and you create a table called
excluded, you're stuck with the ambiguity in the DO UPDATE statement
as
On 2015-05-07 16:15:18 +0300, Heikki Linnakangas wrote:
On 05/07/2015 12:01 AM, Andres Freund wrote:
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
If we don't allow AS whatever, and you create a table called excluded,
On 2015-05-05 15:27:09 +0300, Heikki Linnakangas wrote:
I'm a bit late to the party as I haven't paid much attention to the syntax
before, but let me give some comments on this arbiter index inference
thingie.
To recap, there are three variants:
A. INSERT ... ON CONFLICT DO NOTHING
On 2015-05-06 13:05:16 -0700, Peter Geoghegan wrote:
On Wed, May 6, 2015 at 7:53 AM, Andres Freund and...@anarazel.de wrote:
In this variant, you explicitly specify the constraint by name.
I do think it's a bit sad to not be able to specify unique indexes that
aren't constraints. So I'd
On 05/06/2015 11:05 PM, Peter Geoghegan wrote:
On Wed, May 6, 2015 at 7:53 AM, Andres Freund and...@anarazel.de wrote:
In this variant, you explicitly specify the constraint by name.
I do think it's a bit sad to not be able to specify unique indexes that
aren't constraints. So I'd like to
On 05/07/2015 12:01 AM, Andres Freund wrote:
On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
I'll see about fixing that. It's not just a matter of creating another alias
for the same rel, I'm afraid: foo.t is supposed to refer to the tuple that
we attempted to insert, like it does
On Wed, May 6, 2015 at 1:48 PM, Heikki Linnakangas hlinn...@iki.fi wrote:
TARGET is also very descriptive, because it situationally describes
either the existing tuple actually present in the table, or (from a
RETURNING clause) the final tuple present in the table post-UPDATE.
We use the term
On 2015-05-06 23:48:18 +0300, Heikki Linnakangas wrote:
I'll see about fixing that. It's not just a matter of creating another alias
for the same rel, I'm afraid: foo.t is supposed to refer to the tuple that
we attempted to insert, like it does without the ON CONFLICT.
I'm not sure what you
On 05/07/2015 12:18 AM, Andres Freund wrote:
On 2015-05-07 00:10:22 +0300, Heikki Linnakangas wrote:
Right, that's the idea. Indexes are just an implementation detail -
I think that's a distinction just about no user out there cares about.
Unfortunately you can't create a partial constraint
On Wed, May 6, 2015 at 7:53 AM, Andres Freund and...@anarazel.de wrote:
In this variant, you explicitly specify the constraint by name.
I do think it's a bit sad to not be able to specify unique indexes that
aren't constraints. So I'd like to have a corresponding ON INDEX - which
would be
On Wed, May 6, 2015 at 1:22 PM, Andres Freund and...@anarazel.de wrote:
That it supports exclusion constraints?
But so does just naming the index. I don't think it's significant that
exclusion operators are in pg_constraint -- you could just as easily
name the index, since that's all you
On 2015-05-06 13:37:07 -0700, Peter Geoghegan wrote:
On Wed, May 6, 2015 at 1:22 PM, Andres Freund and...@anarazel.de wrote:
That it supports exclusion constraints?
But so does just naming the index. I don't think it's significant that
exclusion operators are in pg_constraint -- you could
Andres pointed out on IM that the TARGET alias is a bit crummy. In
particular, adding an ON CONFLICT DO UPDATE can make a RETURNING clause
invalid, because we change the alias of the target rel:
create table foo (id int4 primary key, t text);
This works:
postgres=# insert into foo (id, t)
On Wed, May 6, 2015 at 2:01 PM, Andres Freund and...@anarazel.de wrote:
How about
6. The tablename and EXCLUDED? Possibility with the ability to specify
an AS for INSERT INTO foo AS whatever?
From an implementation pov that'd be simple ;)
That's what I wanted to do when I realized what
On 2015-05-07 00:10:22 +0300, Heikki Linnakangas wrote:
Right, that's the idea. Indexes are just an implementation detail -
I think that's a distinction just about no user out there cares about.
Unfortunately you can't create a partial constraint - you'll have to
create a partial index. I
On Tue, May 5, 2015 at 5:27 AM, Heikki Linnakangas hlinn...@iki.fi wrote:
To recap, there are three variants:
A. INSERT ... ON CONFLICT DO NOTHING
No arbiter is specified. This means that a conflict on any unique or
exclusion constraint is not allowed (and will do nothing instead). This
On 05/05/2015 12:16 AM, Peter Geoghegan wrote:
On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas hlinn...@iki.fi wrote:
The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:
INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint
On 2015-05-04 14:16:42 -0700, Peter Geoghegan wrote:
On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas hlinn...@iki.fi wrote:
The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:
INSERT INTO mytable ON CONFLICT ON CONSTRAINT
On Tue, May 5, 2015 at 9:36 AM, Andres Freund and...@anarazel.de wrote:
Theoretically this changes the pictures for FDWs, right? Right now
there's
+para
+ commandINSERT/ with an literalON CONFLICT/ clause is not
+ supported with a unique index inference specification, since a
+
On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas hlinn...@iki.fi wrote:
The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:
INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint UPDATE ...
For the record, I have made this
On Wed, Apr 29, 2015 at 12:09 PM, Robert Haas robertmh...@gmail.com wrote:
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well.
There seem to be a few votes for NEW and OLD. That's what I proposed
originally, and (surprise, surprise)
* Andres Freund (and...@anarazel.de) wrote:
On the other hand it's way more convenient to specify a single
constraint name than several columns and a predicate. I'm pretty sure
there's situations where I a) rather live with a smaller chance of error
during a replacement of the constraint b) if
On 2015-04-29 15:31:59 -0400, Robert Haas wrote:
On Wed, Apr 29, 2015 at 3:13 PM, Stephen Frost sfr...@snowman.net wrote:
I still think that constraints should never be named in the syntax.
I guess I don't see a particular problem with that..? Perhaps I'm
missing something, but if
On Wed, Apr 29, 2015 at 4:09 PM, Simon Riggs si...@2ndquadrant.com wrote:
I dislike the way that ignoring objections for a period leads them to be
potentially discarded. I'd prefer to think that as a community we are able
to listen to people even when they aren't continually present to
On 25 April 2015 at 14:05, Peter Geoghegan p...@heroku.com wrote:
a) Why is is 'CONFLICT? We're talking about a uniquness violation. What
if we, at some later point, also want to handle other kind of
violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ...
I think that
* Andres Freund (and...@anarazel.de) wrote:
On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
* Andres Freund (and...@anarazel.de) wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will
have to
look into
On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
* Andres Freund (and...@anarazel.de) wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will have
to
look into manual for the names of TARGET and EXCLUDED because
On 28 April 2015 at 15:46, Stephen Frost sfr...@snowman.net wrote:
+1, NEW/OLD seem pretty natural and I'm not worried about what they look
like in rules, and their usage in triggers matches up with what they'd
mean here, I'd think.
Since I've stuck my head above the parapet once I figured
On 28/04/15 16:44, Andres Freund wrote:
On 2015-04-28 10:40:10 -0400, Stephen Frost wrote:
* Andres Freund (and...@anarazel.de) wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will have to
look into manual for the names
On 28 April 2015 at 15:57, I wrote:
MySQL uses VALUES(columnname) to reference the intended INSERT value
(what you might term NEW) and the target name to reference OLD. I
understand that people might think the bracketed syntax isn't very pleasant
because that looks like a function, but it
On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost sfr...@snowman.net wrote:
I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
the tuple being added, while OLD is clearly the existing tuple.
Yes, but EXCLUDED is neither the tuple being added, nor is it the new
tuple. It's
On Tue, Apr 28, 2015 at 7:36 AM, Petr Jelinek p...@2ndquadrant.com wrote:
I am also very sure that every time I'll write this statement I will have to
look into manual for the names of TARGET and EXCLUDED because they don't
seem intuitive to me at all (especially the EXCLUDED).
According to
On Tue, Apr 28, 2015 at 7:38 AM, Andres Freund and...@anarazel.de wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will have to
look into manual for the names of TARGET and EXCLUDED because they don't
seem intuitive to me
* Peter Geoghegan (p...@heroku.com) wrote:
On Tue, Apr 28, 2015 at 7:38 AM, Andres Freund and...@anarazel.de wrote:
On 2015-04-28 16:36:28 +0200, Petr Jelinek wrote:
I am also very sure that every time I'll write this statement I will have
to
look into manual for the names of TARGET and
On Tue, Apr 28, 2015 at 9:45 AM, Peter Geoghegan p...@heroku.com wrote:
Yes, but EXCLUDED is neither the tuple being added, nor is it the new
tuple. It's something else entirely.
I mean, nor is it the existing tuple.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list
* Peter Geoghegan (p...@heroku.com) wrote:
On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost sfr...@snowman.net wrote:
I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
the tuple being added, while OLD is clearly the existing tuple.
Yes, but EXCLUDED is neither the tuple
On Tue, Apr 28, 2015 at 9:58 AM, Stephen Frost sfr...@snowman.net wrote:
* Peter Geoghegan (p...@heroku.com) wrote:
On Tue, Apr 28, 2015 at 9:42 AM, Stephen Frost sfr...@snowman.net
wrote:
I agree with that, but how are NEW and OLD ambiguous? NEW is clearly
the tuple being added, while
On Tue, Apr 28, 2015 at 10:36 AM, David G. Johnston
david.g.johns...@gmail.com wrote:
This example exemplifies the poorness of the proposed wording, IMO:
[...]
SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')'
NEW.dname || '(formerly ' || OLD.dname || ')' reads perfectly
On 4/25/15 2:05 PM, Peter Geoghegan wrote:
Note that the syntax is quite similar to the SQLite
syntax of the same feature, that has ON CONFLICT IGNORE (it also has
ON CONFLICT REPLACE, but not ON CONFLICT UPDATE).
I don't know anything about SQLite's syntax, but from the online syntax
diagrams
On Mon, Apr 27, 2015 at 1:19 PM, Peter Eisentraut pete...@gmx.net wrote:
it appears that they are using quite a different syntax. The ON
CONFLICT clause is attached to a constraint, specifying the default
action for that constraint. The INSERT command can then override this
default choice.
On Mon, Apr 27, 2015 at 4:21 PM, Peter Geoghegan p...@heroku.com wrote:
* Don't change the ON CONFLICT spelling.
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem fine to me as well.
* Change the syntax to put the WHERE clause used to infer partial
On Mon, Apr 27, 2015 at 10:20 AM, Bruce Momjian br...@momjian.us wrote:
Agreed, and I like the DO [ UPDATE | NOTHING ] too.
Here is what I think I need to do:
* Don't change the ON CONFLICT spelling.
* Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
TARGET.*). Those seem
On Sun, Apr 26, 2015 at 09:34:12AM -0400, Stephen Frost wrote:
* Heikki Linnakangas (hlinn...@iki.fi) wrote:
On 04/25/2015 12:01 PM, Andres Freund wrote:
INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
UPDATE|IGNORE
My problem with the WHERE being inside the parens
On 04/25/2015 12:01 PM, Andres Freund wrote:
INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial]) UPDATE|IGNORE
My problem with the WHERE being inside the parens in the above is that
it's
a) different from CREATE INDEX
b) unclear whether the WHERE belongs to colb or the whole index
On 26/04/15 12:08, Andres Freund wrote:
On April 26, 2015 11:22:01 AM GMT+02:00, Heikki Linnakangas hlinn...@iki.fi
wrote:
On 04/25/2015 12:01 PM, Andres Freund wrote:
That's why I wanted the WHERE outside the (), which requires either
adding DO between the index inference clause, and the
* Heikki Linnakangas (hlinn...@iki.fi) wrote:
On 04/25/2015 12:01 PM, Andres Freund wrote:
INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
UPDATE|IGNORE
My problem with the WHERE being inside the parens in the above is that
it's
a) different from CREATE INDEX
b) unclear
On April 26, 2015 11:22:01 AM GMT+02:00, Heikki Linnakangas hlinn...@iki.fi
wrote:
On 04/25/2015 12:01 PM, Andres Freund wrote:
INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial])
UPDATE|IGNORE
My problem with the WHERE being inside the parens in the above is
that
it's
a)
On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost sfr...@snowman.net wrote:
What's important, in my view, is to keep the simple case simple and so
I'm not particularly wedded to any of these approaches, just trying to
help with other suggestions.
INSERT INTO mytable VALUES
* Peter Geoghegan (p...@heroku.com) wrote:
On Sun, Apr 26, 2015 at 11:08 AM, Stephen Frost sfr...@snowman.net wrote:
I don't want to accept something that automatically merges the
excluded tuple (e.g., SET (*) = EXLCUDED.*), for reasons outlined
here:
* Peter Geoghegan (p...@heroku.com) wrote:
On Sun, Apr 26, 2015 at 11:35 AM, Stephen Frost sfr...@snowman.net wrote:
Ok, that makes sense.. So is the concern that an INSERT would end up
getting default values while an UPDATE would preserve whatever's there?
I don't see that as an issue.
On Sun, Apr 26, 2015 at 11:35 AM, Stephen Frost sfr...@snowman.net wrote:
Ok, that makes sense.. So is the concern that an INSERT would end up
getting default values while an UPDATE would preserve whatever's there?
I don't see that as an issue.
I think it easily could be.
Are you still
Peter,
* Peter Geoghegan (p...@heroku.com) wrote:
On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost sfr...@snowman.net wrote:
What's important, in my view, is to keep the simple case simple and so
I'm not particularly wedded to any of these approaches, just trying to
help with other
On Sun, Apr 26, 2015 at 11:08 AM, Stephen Frost sfr...@snowman.net wrote:
I don't want to accept something that automatically merges the
excluded tuple (e.g., SET (*) = EXLCUDED.*), for reasons outlined
here: https://wiki.postgresql.org/wiki/UPSERT#VoltDB.27s_UPSERT
Perhaps I'm missing it,
On Sun, Apr 26, 2015 at 11:43 AM, Stephen Frost sfr...@snowman.net wrote:
I think it easily could be.
Ok.. Can you elaborate on that? Would it be an issue that's different
from the same thing done as independent commands?
I think that the stuff I linked to describes my concerns
On 2015-04-25 11:50:59 -0700, Peter Geoghegan wrote:
On Sat, Apr 25, 2015 at 11:24 AM, Andres Freund and...@anarazel.de wrote:
c) Right now the UPDATE can refer to pseudo relations 'TARGET' and
'EXCLUDED'. I think especially the latter doesn't fit anymore at
all. How about
On Sat, Apr 25, 2015 at 2:01 AM, Andres Freund and...@anarazel.de wrote:
My problem with the WHERE being inside the parens in the above is that
it's
a) different from CREATE INDEX
I don't think that that's an important goal.
b) unclear whether the WHERE belongs to colb or the whole index
On Sat, Apr 25, 2015 at 11:24 AM, Andres Freund and...@anarazel.de wrote:
b) unclear whether the WHERE belongs to colb or the whole index
expression. The equivalent for aggregates, which I bet is going to be
used less often, caused a fair amount of confusing.
I don't see those two
On Sat, Apr 25, 2015 at 11:50 AM, Peter Geoghegan p...@heroku.com wrote:
To be fair, there is one unrelated slight difference with RETURNING
and conventional UPDATEs: You cannot return the EXCLUDED tuple (in the
same way that you can reference the joined-FROM tuple within
conventional
On Sat, Apr 25, 2015 at 12:23 PM, Andres Freund and...@anarazel.de wrote:
95% of all users will know NEW/OLD from triggers, not rules. Where NEW
is used in a quite comparable way.
I don't think it's comparable.
Seems pretty descriptive of the situation to me - I actually put a lot
of
On Sat, Apr 25, 2015 at 12:35 PM, Peter Geoghegan p...@heroku.com wrote:
That
it has 'morphing' characteristics imo just makes it worse, rather than
better. Besides being confusing that it has different meanings, it's far
from inconceivable that somebody wants to return values from the
64 matches
Mail list logo
