[PATCH] Implement INSERT SET syntax

From: Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [PATCH] Implement INSERT SET syntax
Date: 2019-07-17 04:30:02
Message-ID: 385970D7-37BF-4D23-A5FD-525BBE18849E@internetnz.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
insert-set-v1.patch application/octet-stream 8.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-07-17 04:35:05 Re: psql ctrl+f skips displaying of one record and displays skipping one line
Previous Message Thomas Munro 2019-07-17 04:18:08 Re: psql ctrl+f skips displaying of one record and displays skipping one line