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