Re: [PATCH] Implement INSERT SET syntax

On 2019-08-16 05:19, Amit Kapila wrote:
> I think this can be a handy feature in some cases as pointed by you,
> but do we really want it for PostgreSQL? In the last round of
> discussions as pointed by you, there doesn't seem to be a consensus
> that we want this feature. I guess before spending too much time into
> reviewing this feature, we should first build a consensus on whether
> we need this.

I think the problem this is attempting to solve is valid.

What I don't like about the syntax is that it kind of breaks the
notional processing model of INSERT in a fundamental way. The model is

INSERT INTO $target $table_source

where $table_source could be VALUES, SELECT, possibly others in theory.

The proposed syntax changes this to only allow a single row to be
specified via the SET syntax, and the SET syntax does not function as a
row or table source in other contexts.

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

I think this could be used for INSERT as well.

And then you need a syntax to assign column names inside the VALUES
rows. I think you could do either of the following:

VALUES (a => 1, b => 2)

or

VALUES (1 AS a, 2 AS b)

Another nice effect of this would be that you could so something like

INSERT INTO tbl2 CORRESPONDING SELECT * FROM tbl1;

which copies the contents of tbl1 to tbl2 if they have the same column
names but allowing for a different column order.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services