|From:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|To:||Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>|
|Cc:||David Steele <david(at)pgmasters(dot)net>, Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>, Michael Paquier <michael(at)paquier(dot)xyz>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: [PATCH] Implement INSERT SET syntax|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> On 2020-03-24 18:57, Tom Lane wrote:
>> No doubt that's all fixable, but the realization that some cases of
>> this syntax are*not* just syntactic sugar for standards-compliant
>> syntax is giving me pause. Do we really want to get out front of
>> the SQL committee on extending INSERT in an incompatible way?
> What is the additional functionality that we are considering adding here?
> The thread started out proposing a more convenient syntax, but it seems
> to go deeper now and perhaps not everyone is following.
AIUI, the proposal is to allow INSERT commands to be written
using an UPDATE-like syntax, for example
INSERT INTO table SET col1 = value1, col2 = value2, ... [ FROM ... ]
where everything after FROM is the same as it is in SELECT. My initial
belief was that this was strictly equivalent to what you could do with
a target-column-names list in standard INSERT, viz
INSERT INTO table (col1, col2, ...) VALUES (value1, value2, ...);
INSERT INTO table (col1, col2, ...) SELECT value1, value2, ... FROM ...
but it's arguably more legible/convenient because the column names
are written next to their values.
However, that rewriting falls down for certain multiassignment cases
where you have a row source that can't be decomposed, such as my
INSERT INTO table SET (col1, col2) = (SELECT value1, value2 FROM ...),
... [ FROM ... ]
So, just as we found for UPDATE, multiassignment syntax is strictly
stronger than plain column-by-column assignment.
There are some secondary issues about which variants of this syntax
will allow a column value to be written as DEFAULT, and perhaps
about whether set-returning functions work. But the major point
right now is about whether its's possible to rewrite to standard
regards, tom lane
|Next Message||Andy Fan||2020-03-25 14:24:42||Re: [PATCH] Keeps tracking the uniqueness with UniqueKey|
|Previous Message||tushar||2020-03-25 14:16:09||Re: [Proposal] Global temporary tables|