Message-ID: <4F3C6480.7040301@gmx.net>
Date: Thu, 16 Feb 2012 03:05:52 +0100
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:10.0.1) Gecko/20120208 Thunderbird/10.0.1
MIME-Version: 1.0
To: David Johnston <polobo@yahoo.com>
CC: pgsql-sql@postgresql.org
Subject: Re: need help with import
References: <4F3C55C2.8070005@gmx.net>
	<013001ccec48$3db033e0$b9109ba0$@yahoo.com>
In-Reply-To: <013001ccec48$3db033e0$b9109ba0$@yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Am 16.02.2012 02:13, schrieb David Johnston:
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas
> Sent: Wednesday, February 15, 2012 8:03 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] need help with import
>
> Hi
> I get CSV files to import.
> Th structure is like this.
> main part, sub part
> Could be like this
>
> A, a1
> A, a2
> A, a3
> B, b1
> B, b2
>
> 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 ( main_id, sub_id ).
> The 2 primary keys main_part.id and sub_part.id are both serials.
>
> Is there a way to do an import with SQL?
>
> 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 INTO sub_part ( ... ) SELECT sub columns FROM import;
>
> But how would I know what main_id and sub_id to insert into the n:m relation?
>
> At first when I do the import the relation is actually 1:n.
>
> --------------------------------------------
>
> You will need to use the temporary table and perform multiple insert+select.
>
> I do not understand where you are confused.  It would help to provide more 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 usually having some descriptive meaning.  Is time one of your components that you are not  showing us?
>
>
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 )

I would read the csv into a temporary table "import" and insert the main 
columns into main_part ().
Then there are new tuples in main_part()
42, A
43, B

Now I insert the sub columns into sub_part()
I'll get e.g.
1000, a1
1001, a2
1002, a3
1003, b1
1004, b2

To fill up main_to_sub ( ... ) I needed to know which sub_id belongs to 
which main_id.
( 42, 1000 )
( 42, 1001 )
( 42, 1002 )
( 43, 1003 )
( 43, 1004 )

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 sub-data-column but this seems to be a wee bit tedious.

Is there a more elegant way hat I don't see, yet?