Skip site navigation (1) Skip section navigation (2)

Multiple table insert using a CSV list as the data source

From: "Norman Khine" <norman(at)khine(dot)net>
To: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Multiple table insert using a CSV list as the data source
Date: 2002-07-14 12:29:52
Message-ID: EIEOIOLCDOLMCNPHEGDOIEDGDKAA.norman@khine.net (view raw or flat)
Thread:
Lists: 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(at)khine(dot)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



Responses

pgsql-novice by date

Next:From: Norman KhineDate: 2002-07-14 16:25:21
Subject: Re: Multiple table insert using a CSV list as the data source
Previous:From: Norman KhineDate: 2002-07-14 11:41:02
Subject: Multiple table insert using a CSV list as the data source

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group