Re: Follow-up on INSERT INTO ... SET ...

Hi Tom,

On 29/01/2019 07.20, Tom Lane wrote:
> Sven Berkvens-Matthijsse <sven(at)postgresql(dot)berkvens(dot)net> writes:
>> In 2016, a thread was started about implementing INSERT INTO ... SET ...
>> that included a patch and was basically ready for inclusion in
>> PostgreSQL. However, it seems as though it stagnated for some reason.
>> Does anybody remember this and is there perhaps someone who knows what
>> the current status is? If nobody is working on this any longer, I'd be
>> willing to try to revive the patch for the current code base.
>> The thread that I'm talking about can be found at:
>> https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61(at)joh(dot)to
>
> Looking at the thread, it seems like Marko lost interest for some
> reason, and never submitted a revised patch.

That was my conclusion too, but I didn't know whether there had been
some off-list discussion that eventually led to the abandonment of the
patch and proposal.

> I'm not really sure whether we'd want to support a nonstandard
> syntax for this. I can see that it'd have some usefulness for wide
> tables, but is that enough of an argument to risk incompatibility
> with future SQL-spec extensions?

I've seen mulitple concerns for this in some messages that I found while
Googling. But this is something that always plays a role when one
decides to deivate from the SQL standard, isn't it?

PostgreSQL would not be the first database system to support the INSERT
INTO ... SET ... syntax, MySQL has had it for a very long time (not that
I've ever used MySQL, but I gathered this from what I've Googled). I
have no idea whether the SQL standard folks take that sort of thing into
account when proposing new features for the SQL standard. But if they
do, there is less risk of running into problems here because the syntax
has already been available in the field for a very long time.

> Looking at the patch itself, I'd raise two major complaints:
>
> * It looks like the only supported syntax is "INSERT ... SET
> set_clause_list", which I guess is meant to be functionally
> the same as INSERT ... VALUES with a single values list.
> That's not terribly compelling. I'd expect to be able to
> use this syntax for multiple inserted rows. Maybe allow
> something like INSERT ... SET ... FROM ..., where the set-list
> entries can use variables emitted by the FROM clause?

Yes, I've thought about this myself. What I ended up thinking about was
allowing both the syntax

INSERT INTO whatever SET a = 1, b = 2, c = 3;

and

INSERT INTO whatever SET (a = 1, b = 2, c = 3), (a = 2, b = 1, d = 5);

Then I decided that that isn't all that great. And I dropped the thought.

Thinking more about your proposal for INSERT INTO ... SET ... FROM ...
something like the following comes to mind. It looks like a nice idea,
but specifically for wide tables, for which this proposal would make the
most sense, you end up writing things like:

INSERT INTO whatever SET a = a, b = b, c = c, d = d, e = e
FROM (SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d, 5 AS e UNION ALL
SELECT 2 AS a, 1 AS b, 6 AS c, 8 AS d, 0 AS e);

Which does not look very nice in my opinion. The SELECT ... UNION ALL
SELECT is not really the problem here because the rows could've come
from some other table or a function call for example. The mostly silly
SET list is what bugs me personally here.

I would already be very happy if the INSERT INTO syntax would support
something like this:

INSERT INTO whatever NATURAL SELECT 1 AS c, 2 AS a, 3 AS b, 4 AS d;

Where the NATURAL (or some other keyword) would mean: look at the
returned columns from the query (or VALUES) and map the values in the
resulting rows to the correct columns in the target table, so that it
doesn't matter in which order you select them. Produced columns that
don't exist in the target table would produce an error. Missing columns
would use defaults in the target table as usual.

Anybody with any thoughts, ideas and/or concerns about this last
proposal with the NATURAL keyword?

The only thing it would not support is explicit DEFAULT values, which
VALUES does allow in an INSERT INTO statement. Not much of a concern
though, INSERT INTO ... SELECT ... doesn't allow it either.

> * If I'm reading it right, it blows off multiple-assignment
> syntax -- that is, "SET (a,b,c) = row-valued-expr" -- with
> the comment
>
> + * This is different from set_clause_list used in UPDATE because the SelectStmt
> + * syntax already does everything you might want to do in an in INSERT.
>
> I'm unimpressed with that reasoning, because the SQL-standard
> syntax already does everything you might want to do with this.

Yes, I agree, why specifically disallow some behavior because something
else already supplies that behavior when you're proposing something that
doesn't really supply any new functionality itself.

> Since this patch was originally submitted, we sweated pretty
> hard to upgrade our support of UPDATE's multiple-assignment
> syntax so that it handles all interesting cases; so I'd want
> INSERT ... SET to be fully on par with UPDATE ... SET if we
> do it at all.

Agreed, it'd have to work in the same way.

> regards, tom lane

With kinds regards,
Sven Berkvens-Matthijsse