From: "Raj Mathur (=?utf-8?b?4KSw4KS+4KSc?=
	=?utf-8?b?IOCkruCkvuCkpeClgeCksA==?=)" <raju@linux-delhi.org>
Reply-To: raju@linux-delhi.org
Organization: Kandalaya
To: pgsql-sql@postgresql.org
Subject: Re: need help with import
Date: Thu, 16 Feb 2012 08:25:29 +0530
User-Agent: KMail/1.13.7 (Linux/3.1.0-1-686-pae; KDE/4.6.5; i686; ; )
References: <4F3C55C2.8070005@gmx.net>
In-Reply-To: <4F3C55C2.8070005@gmx.net>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <201202160825.30122.raju@linux-delhi.org>

On Thursday 16 Feb 2012, Andreas wrote:
> 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?

Is this what you need?

foo=> create table mp(mid serial primary key, m text);
CREATE TABLE
foo=> create table sp(sid serial primary key, s text);
CREATE TABLE
foo=> create table ms(mid int references mp, sid int references sp, 
primary key(mid, sid));
CREATE TABLE
foo=> create temporary table t(m text, s text);
CREATE TABLE
foo=> \copy t from '/tmp/x' csv
foo=> select * from t;
 m | s  
---+----
 A | a1
 A | a2
 A | a3
 B | b1
 B | b2
(5 rows)

foo=> insert into mp(m) (select distinct m from t);
INSERT 0 2
foo=> insert into sp(s) (select distinct s from t);
INSERT 0 5
foo=> select * from mp;
 mid | m 
-----+---
   1 | A
   2 | B
(2 rows)

foo=> select * from sp;
 sid | s  
-----+----
   1 | a1
   2 | a2
   3 | a3
   4 | b1
   5 | b2
(5 rows)

foo=> insert into ms (select mid, sid from mp, sp where (m,s) in (select 
m, s from t));
INSERT 0 5
foo=> select * from ms;
 mid | sid 
-----+-----
   1 |   1
   1 |   2
   1 |   3
   2 |   4
   2 |   5
(5 rows)

foo=> 

Regards,

-- Raj
-- 
Raj Mathur                          || raju@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves           || http://schizoid.in   || D17F