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: David Steele <david(at)pgmasters(dot)net>, Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>, Michael Paquier <michael(at)paquier(dot)xyz>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Implement INSERT SET syntax
Date: 2020-03-25 14:17:55
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> On 2020-03-24 18:57, Tom Lane wrote:
>> No doubt that's all fixable, but the realization that some cases of
>> this syntax are*not* just syntactic sugar for standards-compliant
>> syntax is giving me pause. Do we really want to get out front of
>> the SQL committee on extending INSERT in an incompatible way?

> What is the additional functionality that we are considering adding here?
> The thread started out proposing a more convenient syntax, but it seems
> to go deeper now and perhaps not everyone is following.

AIUI, the proposal is to allow INSERT commands to be written
using an UPDATE-like syntax, for example

INSERT INTO table SET col1 = value1, col2 = value2, ... [ FROM ... ]

where everything after FROM is the same as it is in SELECT. My initial
belief was that this was strictly equivalent to what you could do with
a target-column-names list in standard INSERT, viz

INSERT INTO table (col1, col2, ...) VALUES (value1, value2, ...);
INSERT INTO table (col1, col2, ...) SELECT value1, value2, ... FROM ...

but it's arguably more legible/convenient because the column names
are written next to their values.

However, that rewriting falls down for certain multiassignment cases
where you have a row source that can't be decomposed, such as my

INSERT INTO table SET (col1, col2) = (SELECT value1, value2 FROM ...),
... [ FROM ... ]

So, just as we found for UPDATE, multiassignment syntax is strictly
stronger than plain column-by-column assignment.

There are some secondary issues about which variants of this syntax
will allow a column value to be written as DEFAULT, and perhaps
about whether set-returning functions work. But the major point
right now is about whether its's possible to rewrite to standard

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andy Fan 2020-03-25 14:24:42 Re: [PATCH] Keeps tracking the uniqueness with UniqueKey
Previous Message tushar 2020-03-25 14:16:09 Re: [Proposal] Global temporary tables