I am new to dbadmin, and have to load 2 million records into a db
every month :(
So far we have found that a naive approach will result in the
data taking longer to load than it is valid for.
Initially we were using JDBC to insert or update the table.
Now we are only using JDBC to insert new category records, such as when a
new format or country are encountered.
The actual INSERT or UPDATE statements are written out to an SQL file.
To create the SQL file is estimated at 30 hours, but to updating the
psql -f output.sql db
looks as though it will take forever as it has inserted 177778 records in
I have just dropped a text index to help things along.
So two questions:
1. The primary key is an autoincrement field called id:
CREATE SEQUENCE parts_id_seq start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;
SELECT nextval ( 'parts_id_seq' );
CREATE TABLE "parts" (
"id" int4 DEFAULT nextval ( 'parts_id_seq' ) NOT NULL,
"part_no" character(50) NOT NULL,
The Insert statements do not give the id explicitly.
Would I be better off dropping the auto-increment and then re-applying it?
How do I do this?
2. How do I use COPY?
Presumably I again have to take control of the key values,
drop the index
copy from tab delimited file containing hard ids
create id sequence
modify id definition
I would be very grateful for any comments and any longer term help
can be paid for.
pgsql-sql by date
|Next:||From: Tom Lane||Date: 1999-08-27 21:19:18|
|Subject: Re: [SQL] Newbie dbadmin out of his league |
|Previous:||From: Frank Joerdens||Date: 1999-08-27 17:56:52|
|Subject: Re: [SQL] vacuum fails|