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 |
Date: | 2019-08-26 04:14:11 |
Message-ID: | 20CB5FD8-CA07-49D3-B54C-F2CDF4EDF2EC@internetnz.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Tom,
> 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
>> vs
>> 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;
Gareth
> regards, tom lane
Attachment | Content-Type | Size |
---|---|---|
insert-set-v3.patch | application/octet-stream | 14.0 KB |
From | Date | Subject | |
---|---|---|---|
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) |