Re: Question on imports with foreign keys

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?

On Thu, 08 Dec 2011 08:48:51 +0200, Andreas <maps(dot)on(at)gmx(dot)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")

Am 08.12.2011 09:39, schrieb Emre Hasegeli:
> On Thu, 08 Dec 2011 08:48:51 +0200, Andreas <maps(dot)on(at)gmx(dot)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.

On Thu, 08 Dec 2011 12:10:06 +0200, Andreas <maps(dot)on(at)gmx(dot)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.

On 2011-12-08, Andreas <maps(dot)on(at)gmx(dot)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