From: Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net>
Subject: Re: Idea: INSERT INTO ... NATURAL SELECT ...
To: =?UTF-8?Q?Martin_St=c3=b6cker?= <martin.stoecker@stb-datenservice.de>,
 pgsql-sql@lists.postgresql.org
References: <d15865d1-81fd-a282-7348-ee12692c5c2c@berkvens.net>
 <4a9d68cd-4f6b-dbcf-c899-99fd2415f6b8@stb-datenservice.de>
Message-ID: <7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net>
Date: Mon, 4 Feb 2019 17:19:29 +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: <4a9d68cd-4f6b-dbcf-c899-99fd2415f6b8@stb-datenservice.de>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: nl
Precedence: bulk

Hi Martin,

On 01/02/2019 19.41, Martin Stöcker wrote:
> Hi Sven,
>
> in many cases I prefer "copy from" to import data.  So I can create my 
> test data via spreadsheet and csv.

Sure, that works, but it would then be useful if the COPY command would 
actually read the first line of a CSV file and use it to find the target 
columns in the table, but it does not.

> Sometimes it is helpfull to do some shell stuff to create it or import 
> with psql.

Yes, that definitely works.

> But if you prefer to have column names and column data near to each 
> other, why not using json?
>
> postgres=#create table test ( i integer, t text);
> postgres=# insert into test(select * from 
> json_to_recordset('[{"i":1,"t":"foo"},{"i":"7","t":"bar"}]') as x(i 
> int, t text));

That also works, but requires one to name all the columns and their 
types in the "AS x" part. That makes the statement very verbose. My 
proposal would not require the types to be stated and would only require 
the column names in the "data part" of the statement.

> INSERT 0 2
> postgres=# select * from test;
>  i |  t
> ---+-----
>   1 | foo
>  7 | bar
> (2 rows)
>
> Regards Martin

Thanks for your thoughts!

With kind regards,
Sven