Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 7EC11384474 for ; Thu, 8 Dec 2011 04:40:10 -0400 (AST) Received: from mail.sihirhosting.com ([77.92.143.143]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RYZWQ-0007CD-38 for pgsql-sql@postgresql.org; Thu, 08 Dec 2011 08:40:11 +0000 Received: (qmail 19116 invoked by uid 509); 8 Dec 2011 10:39:39 +0200 Received: from 212.156.129.218 by gaya (envelope-from , uid 507) with qmail-scanner-1.25-st-qms (clamdscan: 0.88.7/2391. spamassassin: 3.0.2. perlscan: 1.25-st-qms. Clear:RC:0(212.156.129.218):SA:0(-95.7/6.7):. Processed in 1.668933 secs); 08 Dec 2011 08:39:39 -0000 X-Spam-Status: No, hits=-95.7 required=6.7 X-Antivirus-MYDOMAIN-Mail-From: emre.hasegeli@tart.com.tr via gaya X-Antivirus-MYDOMAIN: 1.25-st-qms (Clear:RC:0(212.156.129.218):SA:0(-95.7/6.7):. Processed in 1.668933 secs Process 19099) Received: from unknown (HELO tart-hasegeli-development) (emre.hasegeli@tart.com.tr@212.156.129.218) by mail.sihirhosting.com with AES256-SHA encrypted SMTP; 8 Dec 2011 10:39:37 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: Andreas Subject: Re: Question on imports with foreign keys References: <4EE05DD3.2080308@gmx.net> Date: Thu, 08 Dec 2011 10:39:49 +0200 Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 7bit From: "Emre Hasegeli" Organization: =?utf-8?B?VGFydCDEsG50ZXJuZXQgVGVrbm9sb2ppbGVyaSBBxZ4=?= Message-ID: In-Reply-To: <4EE05DD3.2080308@gmx.net> User-Agent: Opera Mail/11.52 (Linux) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/27 X-Sequence-Number: 36088 On Thu, 08 Dec 2011 08:48:51 +0200, 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. > > Those lookup-tables will only be needed for a project with limited life > time so I create a schema that might be called "project_x". There I > create the necessary lookup tables. > > The core of the import will be added to the customers table with > unlimited livespan. The customers table has a PKey id which is a serial. > > I don't want to add FKey columns into customers for the new > lookup-tables so I create another table in project_x "projectinfos" that > stores those FKeys and another FKey that references customers.id. > > First question: Is this a stupid aproach? It is logical to reduce dependency from the temporary schema to the customer table which is on the permanent schema. > > If not: > How is the easiest way to to find the customer.id of the new customers > so I can insert the projectinfos? > It is easy to select rows not related with another table. One of the following queries can be used. > Select * from "customers" where id not in (select "customerId" from > "projectinfos") > Select * from "customers" left join "projectinfos" on "customers"."id" = > "projectinfos"."customerId" where "projectinfos"."customerid" is null > Select * from "customers" where not exists (select true from > "projectinfos" where "customers".id = "customerId")