From: Andreas <maps.on@gmx.net>
To: David Johnston <polobo@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: need help with import
Date: Thu, 16 Feb 2012 03:05:52 +0100
Message-ID: <4F3C6480.7040301@gmx.net> (raw)
In-Reply-To: <013001ccec48$3db033e0$b9109ba0$@yahoo.com>
References: <4F3C55C2.8070005@gmx.net>
<013001ccec48$3db033e0$b9109ba0$@yahoo.com>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net, polobo@yahoo.com
Subject: Re: need help with import
In-Reply-To: <4F3C6480.7040301@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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