Efficiently move data from one table to another, with FK constraints?

Rob W wrote:

I am using COPY to bulk load large volumes (i.e. multi GB
range) of data to a staging table in a PostgreSQL 8.3. For
performance, the staging table has no constraints, no primary
key, etc. I want to move that data into the "real" tables,
but need some advice on how to do that efficiently.

Here's a simple, abbreviated example of tables and relations
I'm working with (in reality there are a lot more columns and
foreign keys).

/* The raw bulk-loaded data. No indexes or constraints. */
CREATE TABLE log_entry (
req_time TIMESTAMP NOT NULL,
url TEXT NOT NULL,
bytes INTEGER NOT NULL
);

/* Where the data will be moved to. Will have indexes, etc */
CREATE TABLE request (
id BIGSERIAL PRIMARY KEY,
req_time TIMESTAMP WITH TIME ZONE NOT NULL,
bytes INTEGER NOT NULL,
fk_url INTEGER REFERENCES url NOT NULL,
);

CREATE TABLE url (
id SERIAL PRIMARY KEY,
path TEXT UNIQUE NOT NULL,
);

Is there a way to move this data in bulk efficiently?
Specifically I'm wondering how to handle the foreign keys?
The naive approach is:

1) For each column that is a foreign key in the target table,
do INSERT ... SELECT DISTINCT ... to copy all the values
into the appropriate child tables.
2) For each row in log_entry, do a similar insert to insert
the data with the appropriate foreign keys.
3) delete the contents of table log_entry using TRUNCATE

Obviously, this would be very slow when handling tens of
millions of records. Are there faster approaches to solving
this problem?

How about something like that:

INSERT INTO url (path) (SELET DISTINCT url FROM log_entry);

Then

INSERT INTO request (req_time, bytes, fk_url)
(SELECT l.req_time, l.bytes, u.id FROM log_entry AS l JOIN url AS u ON (l.url = u.path));

I didn't test it, so there may be syntax errors and stuff.

But I doubt that it can be done much more efficiently.

Creating an index on log_entry(url) *might* improve performance. Check with EXPLAIN.

The TRUNCATE should not be a very expensive operation.

Yours,
Laurenz Albe