Idea: INSERT INTO ... NATURAL SELECT ...

Hi everyone,

I started a discussion on the hackers mailing list regarding a patch
that was made more than two years ago but was never merged for lack of
time then and lack of interest and time now (see
https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61(at)joh(dot)to
for the original thread and
https://www.postgresql.org/message-id/840eb7b0-17ae-fe52-1643-cd7395eed5df%40berkvens.net
for the new thread). During that discussion, I arrived at the standpoint
that the original idea was perhaps not the best idea to start with after
all.

I write quite some handmade SQL, and usually, SQL allows me to write
what I want to express in a compact, clear fashion. There is one
exception that I keep running into. I frequently need to insert manually
entered values into various tables with many columns. Of course, INSERT
INTO ... (...) {SELECT | VALUES} ... allows me to do just that, however,
it is very hard to find which values will go into which column that way:
column names and values are not near to each other in this syntax.

The patch that I initially mailed about implements a syntax that MySQL
has had for a long time: INSERT INTO ... SET a = 1, b = 2, etc. That
looks okay on first sight but has several drawbacks. One is that it is
non-standard, but a more significant drawback is that it allows only one
row to be inserted per statement.

The idea that I got was the following, and I'd like to know what other
people think about this. If other people think it's a good idea too and
nobody has objections against it, I'd be willing to try and create a
patch for an implementation.

The syntax that I'm proposing is:

INSERT INTO table NATURAL query
       [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

The idea of the NATURAL keyword is that one does not explicitly name the
columns of the target table in parentheses, and does not use its default
ordering of columns if one does not name any columns, but instead have
the NATURAL keyword trigger behavior where the column names to be used
in the target table are determined from the column names produced by the
query instead. For example:

CREATE TABLE test_table(a INTEGER, b INTEGER, c INTEGER, d INTEGER);

These two would be equivalent:

INSERT INTO test_table (b, c, a) VALUES (3, 5, 7);

INSERT INTO test_table NATURAL SELECT 3 AS b, 5 AS c, 7 AS a;

As would these two:

INSERT INTO test_table (b, c, a)
     SELECT source.foo, source.bar, source.beep FROM source;

INSERT INTO test_table NATURAL SELECT
     source.foo AS b, source.bar AS c, source.beep AS a FROM source;

These examples are of course contrived, and the benefit only really
shows when the table has many columns.

The query would fail to execute if one (or more) of the column names in
the query does not exist in the target table, or if a column name is
used more than once in the query. Everything else works just as you
would expect, the behavior is identical to using a normal SELECT or
VALUES where one specifies the column names by hand. So, specifically
highlighting the differences, these queries would fail:

INSERT INTO test_table NATURAL SELECT
      1 AS does_not_exist, 2 AS also_nonexistent;

INSERT INTO test_table NATURAL SELECT 1 AS a, 2 AS a;

Anyone with any thoughts about this? An implementation would make
inserting data into wide tables by hand very much easier. Because of the
placement of the NATURAL keyword, I don't think this will conflict with
any current or future proposal from the SQL standard (except maybe for
this one :-) ).

With kind regards,
Sven Berkvens-Matthijsse

PS I have not subscribed to the SQL list yet, please copy me in explicitly.