Hello,
Attached is a patch that adds the option of using SET clause to specify
the columns and values in an INSERT statement in the same manner as that
of an UPDATE statement.
A simple example that uses SET instead of a VALUES() clause:
INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';
Values may also be sourced from a CTE using a FROM clause:
WITH x AS (
SELECT 'foo' AS c1, 'bar' AS c2, 'baz' AS c3
)
INSERT INTO t SET c1 = x.c1, c2 = x.c2, c3 = x.c3 FROM x;
The advantage of using the SET clause style is that the column and value
are kept together, which can make changing or removing a column or value from
a large list easier.
Internally the grammar parser converts INSERT SET without a FROM clause into
the equivalent INSERT with a VALUES clause. When using a FROM clause it becomes
the equivalent of INSERT with a SELECT statement.
There was a brief discussion regarding INSERT SET on pgsql-hackers in late
August 2009 [1].
INSERT SET is not part of any SQL standard (that I am aware of), however this
syntax is also implemented by MySQL [2]. Their implementation does not support
specifying a FROM clause.
Patch also contains regression tests and documentation.
Regards,
Gareth
[1] https://www.postgresql.org/message-id/flat/2c5ef4e30908251010s46d9d566m1da21357891bab3d%40mail.gmail.com
[2] https://dev.mysql.com/doc/refman/8.0/en/insert.html
