Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1gqgii-0001Ub-FI
	for pgsql-sql@arkaria.postgresql.org; Mon, 04 Feb 2019 16:03:40 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1gqgig-00088v-JS
	for pgsql-sql@arkaria.postgresql.org; Mon, 04 Feb 2019 16:03:38 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <sven@postgresql.berkvens.net>)
	id 1gqgig-00088n-9E
	for pgsql-sql@lists.postgresql.org; Mon, 04 Feb 2019 16:03:38 +0000
Received: from mail.csaw.nl ([5.39.81.230])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <sven@postgresql.berkvens.net>)
	id 1gqgic-00020J-Qk
	for pgsql-sql@lists.postgresql.org; Mon, 04 Feb 2019 16:03:36 +0000
Received: from [10.101.101.129] (BeLinked-3.iae.nl [212.61.45.11])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(No client certificate requested)
	by mail.csaw.nl (Postfix) with ESMTPSA id BD0E234041A;
	Mon,  4 Feb 2019 17:03:31 +0100 (CET)
Subject: Re: Idea: INSERT INTO ... NATURAL SELECT ...
To: Steve Midgley <science@misuse.org>
Cc: pgsql-sql@lists.postgresql.org
References: <d15865d1-81fd-a282-7348-ee12692c5c2c@berkvens.net>
 <CAJexoSL1ZyY4UvhWF6PHaSBw=CFMLY5WuDzbdwU_CwVZ=BLZDw@mail.gmail.com>
From: Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net>
Message-ID: <59a9d777-b160-345c-3a27-cb325f3f1e1d@berkvens.net>
Date: Mon, 4 Feb 2019 17:03:31 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CAJexoSL1ZyY4UvhWF6PHaSBw=CFMLY5WuDzbdwU_CwVZ=BLZDw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

Hi Steve,

On 01/02/2019 18.32, Steve Midgley wrote:
> On Fri, Feb 1, 2019 at 8:07 AM Sven Berkvens-Matthijsse 
> <sven@postgresql.berkvens.net <mailto:sven@postgresql.berkvens.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).

Sure, that's definitely an option! The INSERT statements I have are 
usually part of larger SQL files that do other things as well, but 
there's no reason I couldn't make a preprocessor or something that 
expands something custom into the usually formatted INSERT statements.

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

Maybe it's just not useful that often because people don't write much 
SQL by hand these days (maybe they never did, I don't know).

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

I'm afraid the plug-in architecture of PostgreSQL wouldn't allow my 
proposal to be implemented that way (unless I'm mistaken and I didn't 
read and/or understand correctly).

> Sincerely,
> Steve


Thanks for your response and thoughts!
Sven