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

On Fri, Feb 1, 2019 at 8:07 AM Sven Berkvens-Matthijsse <
sven(at)postgresql(dot)berkvens(dot)net> wrote:

> <snip>
> 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 :-) ).
>

Hi Sven,

I can clearly see the benefits of your proposal from a custom SQL writing
perspective. Personally, I would just write this capability as a DSL in a
higher level language and have it translated into the ugly SQL you're
trying to avoid (that has the field values and names split far apart,
visually). I have no idea what the implications of your NATURAL keyword on
the implementation of Postgres itself, but I do think (as another person
who writes manual SQL on occasion) that your proposal has merits for this
narrow use-case. Personally I don't think the use-case is substantial
enough to merit implementing a custom keyword unless there are a lot more
people with use-cases that would benefit also. But if you can write an
optional module that can be installed outside of core, it seems like why
not? Just my two cents.

Sincerely,
Steve