Re: need help with import

Am 16.02.2012 02:13, schrieb David Johnston:
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andreas
> Sent: Wednesday, February 15, 2012 8:03 PM
> To: pgsql-sql(at)postgresql(dot)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?