Hi all,
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
database with:
psql -f output.sql db
looks as though it will take forever as it has inserted 177778 records in
43 hours.
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,"deleted"
bool,"updatedate"timestamp
);
The Insert statements do not give the id explicitly. Would I be better off dropping the auto-increment and then
re-applyingit? 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
hardids create id sequence create index modify id definition
I would be very grateful for any comments and any longer term help
can be paid for.
yours
Tim Pizey
http://www.paneris.co.uk/
