agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net>
To: Martin Stöcker <martin.stoecker@stb-datenservice.de>
To: pgsql-sql@lists.postgresql.org
Subject: Re: Idea: INSERT INTO ... NATURAL SELECT ...
Date: Mon, 4 Feb 2019 17:19:29 +0100
Message-ID: <7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net> (raw)
In-Reply-To: <4a9d68cd-4f6b-dbcf-c899-99fd2415f6b8@stb-datenservice.de>
References: <d15865d1-81fd-a282-7348-ee12692c5c2c@berkvens.net>
<4a9d68cd-4f6b-dbcf-c899-99fd2415f6b8@stb-datenservice.de>
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
view thread (5+ messages)
Message-ID: <7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net>
Permalink: ../7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net/
Also on: postgresql.org/message-id/7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sven@postgresql.berkvens.net, martin.stoecker@stb-datenservice.de, pgsql-sql@lists.postgresql.org
Subject: Re: Idea: INSERT INTO ... NATURAL SELECT ...
In-Reply-To: <7edf1575-d544-f643-e331-d340b26e87b4@berkvens.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox