Multiple table insert using a CSV list as the data source - Mailing list pgsql-novice

Hello,
I have a postgre database and I have a list of about 12,000 entries. My
problem in short is that I am trying to transfer an Access database to
Postgres and I am trying to match a very broken Access db schema an input
the data onto an sort of OK postgre schema - if this makes any sense;^)

Anyway, I am able to extract the data from the Access db into a sort of
managable file, but I am having difficulties in writing the sql insert
statement so that ALL parts of the dataabse are filled in at the same time.

Here is the csv table and its fields:

CREATE TABLE "csv" (
    "PRODUCT_ID" character varying(30),
    "TYPE" character varying(15),
    "ARTIST" character varying(254),
    "DESCRIPTION" character varying(254),
    "CATEGORY" character varying(30),
    "CATEGORY2" character varying(30),
    "CATEGORY3" character varying(30),
    "LABEL" character varying(50),
    "CATEGORY4" character varying(30),
    "IMAGE_PATH" character varying(1),
    "PRICE" character varying(15),
    "LONG_DESCRIPTION" character varying(254),
    "SHIP_RATE" smallint,
    "WRITE_UP" character varying(1),
    "TRACK_NO" smallint,
    "TRACK_NAME" character varying(254),
    "AUDIO" character varying(1)
);


**The one thing to note here is that Category, Category_2, Category_3 are in
the following format

'CD', 'Big Band / Swing', 'Swing'

and not by category.id

Now here are the three tables that need to be updated from this one file:

CREATE SEQUENCE "product_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;

CREATE TABLE "product" (
    "id" integer DEFAULT nextval('"product_id_seq"'::text) NOT NULL,
    "status" integer,
    "manufacturer" integer,
    "name" character varying(128),
    "model" character varying(128),
    "shortdesc" text,
    "longdesc" text,
    "smallimage" character varying(128),
    "largeimage" character varying(128),
    "xlargeimage" character varying(128),
    "pdf" character varying(128),
    "manual_url" character varying(128),
    "keywords" text,
    "weight" numeric(8,3),
    "volumetric" numeric(8,3),
    "supplier" integer,
    "supplierpart" character varying(128),
    "vatrate" integer
);

CREATE TABLE "price" (
    "id" integer DEFAULT nextval('"price_id_seq"'::text) NOT NULL,
    "product" integer,
    "type" integer,
    "minqty" integer,
    "price" numeric(8,2)
);


CREATE TABLE "category_product" (
    "category" integer,
    "product" integer
);



CREATE SEQUENCE "category_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;


CREATE TABLE "category" (
    "id" integer DEFAULT nextval('"category_id_seq"'::text) NOT NULL,
    "name" character varying(128),
    "parent" integer,
    "description" character varying(255),
    "view" integer,
    "keywords" text,
    "featured_1" integer,
    "featured_2" integer,
    "visible" integer DEFAULT 1
);

INSERT INTO "category" VALUES (376,'',366,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (-1,'_Home',1,NULL,2,NULL,NULL,NULL,0);
INSERT INTO "category" VALUES (3,'Video',1,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (4,'Big Band /
Swing',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (2,'CDs',1,'',2,NULL,25,NULL,1);
INSERT INTO "category" VALUES (5,'Blues / Soul',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (6,'Chart',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (7,'Classical /
Opera',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (8,'Country',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (9,'Jazz',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (10,'Popular',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (11,'Rock N Roll /
Rock',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (12,'Shows / Films',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (13,'Various',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (14,'World',2,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (1,'All
Products',NULL,NULL,NULL,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (15,'Adult',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (16,'Cartoon',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (17,'Children',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (18,'Documentries',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (19,'Education',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (20,'Feature Films',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (21,'Music',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (23,'Sport',3,'',2,NULL,NULL,NULL,1);
INSERT INTO "category" VALUES (22,'Special
Interest',3,'',2,NULL,NULL,NULL,1);


CREATE UNIQUE INDEX "category_id_key" on "category" using btree ( "id"
"int4_ops" );

SELECT setval ('"category_id_seq"', 23, 't');


I hope this makes some sense.

Thanks

Norman


zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zz/********/z/****\zzz|****\*\zz|*******|z
z/^^^^^^^^/z/******\zz|*^^^^|*|z|*|^^^^^|z norman khine
zzzzzz/**/z|**/^^\**|z|*|zzz|*|z|*|zzzzzzz mailto:norman@khine.net
zzzzz/**/zz|*|zzzz|*|z|****/*/zz|*****|zzz purley
z/******/zz|*|zzzz|*|z|*|^^zzzzz|*|^^^|zzz UK
zzZ/**/zzzz|**\^^/**|z|*|zzzzzzz|*|zzzzzzz
zz/******/zz\******/zz|*|zzzzzzz|*|*****|z
z/^^^^^^/zzzz\^^^^/zzz|^|zzzzzzz|^^^^^^^|z
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz