Idea: INSERT INTO ... NATURAL SELECT ... - Mailing list pgsql-sql

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@joh.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.