Re: [PATCH] Implement INSERT SET syntax

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>, Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Implement INSERT SET syntax
Date: 2019-08-18 15:00:09
Message-ID: 32718.1566140409@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jürgen Purtz 2019-08-18 15:29:30 Re: pgsql: doc: Add some images
Previous Message Nino Floris 2019-08-18 14:56:27 [PATCH] ltree, lquery, and ltxtquery binary protocol support