bulk inserts

From: Dave Huber <DHuber(at)letourneautechnologies(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: bulk inserts
Date: 2009-09-28 15:38:05
Message-ID: 7CDADB576E07AC4FA71E1B12566C9126540E09FA9F@lti-mb-1.LTI.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, I'm fairly new to postgres and am having trouble finding what I'm looking for. Is there a feature that allows bulk inserts into tables? My setup is Win XPe 2002 SP3 and PostgreSQL 8.3. I need to add entries from a file where each file contains 250 - 500 records. The files are created by a third-party and read/written as binary. The table looks like the following:

CREATE TABLE data_log_20msec_table
(
log_id bigserial NOT NULL,
timestamp_dbl double precision,
data bytea,
CONSTRAINT data_log_20msec_table_pkey PRIMARY KEY (log_id)
)
WITH (OIDS=FALSE);
ALTER TABLE data_log_20msec_table OWNER TO postgres;
-- Index: data_log_20msec_table_timestamp_index
-- DROP INDEX data_log_20msec_table_timestamp_index;
CREATE INDEX data_log_20msec_table_timestamp_index
ON data_log_20msec_table
USING btree
(timestamp_dbl);
The current method for transferring records from the file to postgres is using a prepared statement that is called iteratively on each record read from the file:

INSERT INTO data_log_20msec_table (timestamp_dbl,data) VALUES ($1::double precision,$2::bytea)

Using COPY is out of the question as the file is not formatted for that and since other operations need to occur, the file needs to be read sequentially anyway.

Any amount of help would be gladly excepted, even if it's pointing me to another thread or somewhere in the manual. Thanks,

Dave Huber

________________________________
This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2009-09-28 15:51:46 Re: bulk inserts
Previous Message John 2009-09-28 15:32:09 sync structures