From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: Question on imports with foreign keys
Date: 10 Dec 2011 23:01:11 GMT
Message-ID: <jc0obn$521$1@reversiblemaps.ath.cx> (raw)
References: <4EE05DD3.2080308@gmx.net>
On 2011-12-08, Andreas <maps.on@gmx.net> wrote:
> Hi,
>
> suppose you need to import a csv with standard ciolums like name,
> adress, phone, ... and some additional text columns that need to be
> split off into referenced tables.
...
> How is the easiest way to to find the customer.id of the new customers
> so I can insert the projectinfos?
create table tmp.customer (id integer, name text, addr text)
copy tmp.customer ( id,name,addr ) from stdin ;
...
alter table tmp.customer add column new_id integer default nextval('customer_id.seq'::regclass);
(here the default is the same default that the customer table uses for
its id.)
now you can use "insert ... select ..." to insert these new records
explicitly using new_id to fill the id column of the customer table.
iport the other csv data into similar tables also
and use join on the old id in tmp.customer to get the new id for
copying the other imported tabled.
--
⚂⚃ 100% natural
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: jasen@xnet.co.nz
Subject: Re: Question on imports with foreign keys
In-Reply-To: <jc0obn$521$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox