|From:||Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>|
|To:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|Cc:||Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, 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|
> On 19/08/2019, at 3:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
>> What I don't like about the syntax is that it kind of breaks the
>> notional processing model of INSERT in a fundamental way.
> Agreed. I really don't like that this only works for a VALUES-like case
> (and only the one-row form at that). It's hard to see it as anything
> but a wart pasted onto the syntax.
>> Let's think about how we can achieve this using existing concepts in
>> SQL. What we really need here at a fundamental level is an option to
>> match $target to $table_source by column *name* rather than column
>> *position*. There is existing syntax in SQL for that, namely
>> a UNION b
>> a UNION CORRESPONDING b
> A potential issue here --- and something that applies to Vik's question
> as well, now that I think about it --- is that CORRESPONDING breaks down
> in the face of ALTER TABLE RENAME COLUMN. Something that had been a
> legal query before the rename might be invalid, or mean something quite
> different, afterwards. This is really nasty for stored views/rules,
> because we have neither a mechanism for forbidding input-table renames
> nor a mechanism for revalidating views/rules afterwards. Maybe we could
> make it go by resolving CORRESPONDING in the rewriter or planner, rather
> than in parse analysis; but that seems quite unpleasant as well.
> Changing our conclusions about the data types coming out of a UNION
> really shouldn't happen later than parse analysis.
> The SET-style syntax doesn't have that problem, since it's explicit
> about which values go into which columns.
> Perhaps the way to resolve Peter's objection is to make the syntax
> more fully like UPDATE:
> INSERT INTO target SET c1 = x, c2 = y+z, ... FROM tables-providing-x-y-z
> (with the patch as-submitted corresponding to the case with an empty
> FROM clause, hence no variables in the expressions-to-be-assigned).
> Of course, this is not functionally distinct from
> INSERT INTO target(c1,c2,...) SELECT x, y+z, ... FROM tables-providing-x-y-z
> and it's fair to question whether it's worth supporting a nonstandard
> syntax just to allow the target column names to be written closer to
> the expressions-to-be-assigned.
Thanks for the feedback. Attached is version 3 of the patch that makes
the syntax work more like an UPDATE statement when a FROM clause is used.
So, an updated summary of the new syntax is:
1. Equivalent to VALUES(...):
INSERT INTO t SET c1 = x, c2 = y, c3 = z;
2. Equivalent to INSERT INTO ... SELECT ...:
INSERT INTO t SET c1 = sum(x.c1) FROM x WHERE x.c1 < y AND x.c2 != z
GROUP BY x.c3 ORDER BY x.c4 ASC LIMIT a OFFSET b;
> regards, tom lane
|Next Message||Michael Paquier||2019-08-26 04:27:54||Re: Statement timeout in pg_rewind|
|Previous Message||Moon, Insung||2019-08-26 03:08:22||Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)|