Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 61B9C7412C2 for ; Sat, 10 Dec 2011 19:05:29 -0400 (AST) Received: from lo.gmane.org ([80.91.229.12]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RZVyy-0001VH-G4 for pgsql-sql@postgresql.org; Sat, 10 Dec 2011 23:05:30 +0000 Received: from list by lo.gmane.org with local (Exim 4.69) (envelope-from ) id 1RZVyk-0005NV-3x for pgsql-sql@postgresql.org; Sun, 11 Dec 2011 00:05:14 +0100 Received: from ip-118-90-28-111.xdsl.xnet.co.nz ([118.90.28.111]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 11 Dec 2011 00:05:14 +0100 Received: from jasen by ip-118-90-28-111.xdsl.xnet.co.nz with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Sun, 11 Dec 2011 00:05:14 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: Jasen Betts Subject: Re: Question on imports with foreign keys Date: 10 Dec 2011 23:01:11 GMT Organization: Dis (not Dat) Organisation Lines: 33 Message-ID: References: <4EE05DD3.2080308@gmx.net> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@dough.gmane.org X-Gmane-NNTP-Posting-Host: ip-118-90-28-111.xdsl.xnet.co.nz X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-18 (Linux) X-Pg-Spam-Score: -6.1 (------) X-Archive-Number: 201112/46 X-Sequence-Number: 36107 On 2011-12-08, Andreas 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