pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on imports with foreign keys
5+ messages / 3 participants
[nested] [flat]
* Question on imports with foreign keys
@ 2011-12-08 06:48 Andreas <maps.on@gmx.net>
2011-12-08 08:39 ` Re: Question on imports with foreign keys Emre Hasegeli <emre.hasegeli@tart.com.tr>
2011-12-10 23:01 ` Re: Question on imports with foreign keys Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 5+ messages in thread
From: Andreas @ 2011-12-08 06:48 UTC (permalink / raw)
To: pgsql-sql
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?
If not:
How is the easiest way to to find the customer.id of the new customers
so I can insert the projectinfos?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on imports with foreign keys
2011-12-08 06:48 Question on imports with foreign keys Andreas <maps.on@gmx.net>
@ 2011-12-08 08:39 ` Emre Hasegeli <emre.hasegeli@tart.com.tr>
2011-12-08 10:10 ` Re: Question on imports with foreign keys Andreas <maps.on@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Emre Hasegeli @ 2011-12-08 08:39 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Thu, 08 Dec 2011 08:48:51 +0200, 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.
>
> 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")
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on imports with foreign keys
2011-12-08 06:48 Question on imports with foreign keys Andreas <maps.on@gmx.net>
2011-12-08 08:39 ` Re: Question on imports with foreign keys Emre Hasegeli <emre.hasegeli@tart.com.tr>
@ 2011-12-08 10:10 ` Andreas <maps.on@gmx.net>
2011-12-09 09:39 ` Re: Question on imports with foreign keys Emre Hasegeli <emre.hasegeli@tart.com.tr>
0 siblings, 1 reply; 5+ messages in thread
From: Andreas @ 2011-12-08 10:10 UTC (permalink / raw)
To: Emre Hasegeli <emre.hasegeli@tart.com.tr>; +Cc: pgsql-sql
Am 08.12.2011 09:39, schrieb Emre Hasegeli:
> On Thu, 08 Dec 2011 08:48:51 +0200, Andreas <maps.on@gmx.net> wrote:
>
>> 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")
>
I'm sorry I wasn't clear enough describing the scenario.
Lets's say there were already 1000 records in the customers table.
Now I add 357 new customers to this table.
If I use one of your queries I'd get all 1357 entries of customers since
"project_x.projectinfos" would be newly created for this project and
therefor empty.
I need to know which customers.id was created for which line in the
temporary table that I read in with copy.
When I have those ids I can fill "project_x.projectinfos" with just
those new 357 customer.ids and foreign keys refering the new lookup-tables.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on imports with foreign keys
2011-12-08 06:48 Question on imports with foreign keys Andreas <maps.on@gmx.net>
2011-12-08 08:39 ` Re: Question on imports with foreign keys Emre Hasegeli <emre.hasegeli@tart.com.tr>
2011-12-08 10:10 ` Re: Question on imports with foreign keys Andreas <maps.on@gmx.net>
@ 2011-12-09 09:39 ` Emre Hasegeli <emre.hasegeli@tart.com.tr>
0 siblings, 0 replies; 5+ messages in thread
From: Emre Hasegeli @ 2011-12-09 09:39 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Thu, 08 Dec 2011 12:10:06 +0200, Andreas <maps.on@gmx.net> wrote:
> Lets's say there were already 1000 records in the customers table.
> Now I add 357 new customers to this table.
> If I use one of your queries I'd get all 1357 entries of customers
> since "project_x.projectinfos" would be newly created for this project
> and therefor empty.
> I need to know which customers.id was created for which line in the
> temporary table that I read in with copy.
> When I have those ids I can fill "project_x.projectinfos" with just
> those new 357 customer.ids and foreign keys refering the new
> lookup-tables.
Sorry, I do not understand exactly. What is the difference between the new
357 customers and the existent 1000 customers? If it is the date they
inserted, then the should be filtered by the date. If it is another
attribute like project they are related, then they should be filtered by
the project column or the relation table.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question on imports with foreign keys
2011-12-08 06:48 Question on imports with foreign keys Andreas <maps.on@gmx.net>
@ 2011-12-10 23:01 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2011-12-10 23:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-12-10 23:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-08 06:48 Question on imports with foreign keys Andreas <maps.on@gmx.net>
2011-12-08 08:39 ` Emre Hasegeli <emre.hasegeli@tart.com.tr>
2011-12-08 10:10 ` Andreas <maps.on@gmx.net>
2011-12-09 09:39 ` Emre Hasegeli <emre.hasegeli@tart.com.tr>
2011-12-10 23:01 ` Jasen Betts <jasen@xnet.co.nz>
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