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 || r...@kandalaya.org || GPG: http://otheronepercent.blogspot.com || http://kandalaya.org || CC68 It is the mind that moves || http://schizoid.in || D17F -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
