DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=tZu+GDYASMWS8G/I/yf2kYvTYFutZOmfnTZ6oOvCYFyHl1rfhspdeu2r19XX4tLnMg8s4MB0P+KJvphJnTB3I8J0tqkppooEiOUEjBjEh7C4yKh7mLW7tPSXNoLeoYrbx4R2NZ0EWOLgBKQtSoqISiKsNuoN9FDFGrgFmZVvc3M=
	;
References: <4F3C55C2.8070005@gmx.net>
	<013001ccec48$3db033e0$b9109ba0$@yahoo.com>
	<4F3C6480.7040301@gmx.net>
In-Reply-To: <4F3C6480.7040301@gmx.net>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <D4BF5726-54B8-4887-B038-F8AA3E1792FF@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: need help with import
Date: Wed, 15 Feb 2012 21:41:43 -0500
To: Andreas <maps.on@gmx.net>

On Feb 15, 2012, at 21:05, Andreas <maps.on@gmx.net> wrote:

> Am 16.02.2012 02:13, schrieb David Johnston:
>> -----Original Message-----
>> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.o=
rg] On Behalf Of Andreas
>> Sent: Wednesday, February 15, 2012 8:03 PM
>> To: pgsql-sql@postgresql.org
>> Subject: [SQL] need help with import
>>=20
>> Hi
>> I get CSV files to import.
>> Th structure is like this.
>> main part, sub part
>> Could be like this
>>=20
>> A, a1
>> A, a2
>> A, a3
>> B, b1
>> B, b2
>>=20
>> The database has a table for main_part and one for sub_part.
>> The relation needs to be n:m so there is a relation table that holds ( ma=
in_id, sub_id ).
>> The 2 primary keys main_part.id and sub_part.id are both serials.
>>=20
>> Is there a way to do an import with SQL?
>>=20
>> I can read the CSV into a temporary table and I can do a INSERT INTO main=
_part ( ... ) SELECT DISTINCT main columns FROM import; as well as a INSERT I=
NTO sub_part ( ... ) SELECT sub columns FROM import;
>>=20
>> But how would I know what main_id and sub_id to insert into the n:m relat=
ion?
>>=20
>> At first when I do the import the relation is actually 1:n.
>>=20
>> --------------------------------------------
>>=20
>> You will need to use the temporary table and perform multiple insert+sele=
ct.
>>=20
>> I do not understand where you are confused.  It would help to provide mor=
e meaningful sample data and/or the final result you are trying to achieve. =
 Keep in mind any n:m setup requires three tables with the joining table usu=
ally having some descriptive meaning.  Is time one of your components that y=
ou are not  showing us?
>>=20
>>=20
> As you say there are 3 tables
> main_part ( id serial primary key, ... )
> sub_part ( id serial primary key, ... )
> main_to_sub ( main_id, sub_id )
>=20
> I would read the csv into a temporary table "import" and insert the main c=
olumns into main_part ().
> Then there are new tuples in main_part()
> 42, A
> 43, B
>=20
> Now I insert the sub columns into sub_part()
> I'll get e.g.
> 1000, a1
> 1001, a2
> 1002, a3
> 1003, b1
> 1004, b2
>=20
> To fill up main_to_sub ( ... ) I needed to know which sub_id belongs to wh=
ich main_id.
> ( 42, 1000 )
> ( 42, 1001 )
> ( 42, 1002 )
> ( 43, 1003 )
> ( 43, 1004 )
>=20
> I could compare every main-column in "import" to every related data-column=
 in main_part to get the newly created main_id and do the same with every su=
b-data-column but this seems to be a wee bit tedious.
>=20
> Is there a more elegant way hat I don't see, yet?
>=20

Ditch the whole idea of using a sequence and take your primary keys from the=
 source data. Otherwise yes, you will need to perform the join between the i=
mport and live tables to determine the newly created identifier.

The question to answer is if you see the same values in subsequent import fi=
les do you create a new sequence value or reuse the existing value?  Why?

David J.=