Generating synthetic keys on copy

From: Scott Ribe <scott_ribe(at)killerbytes(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Generating synthetic keys on copy
Date: 2006-10-04 23:16:17
Message-ID: C1499CE1.56E0F%scott_ribe@killerbytes.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Given a table def something like: create table mytbl (id int8 default
nextval('myseq') primary key...

I have data I'm extracting from a legacy database. I want to assign
newly-generated synthetic keys. I would like to use copy to get the data in.
If I put an explicit null in the data file to be imported, pg won't generate
a key, right? Default values only get generated when an INSERT doesn't list
the column. The choices I see are:

- Manually bump the sequence up enough to accommodate the new records, and
assign those ids going into the text file, before import.

- Put a trigger on the table for the import.

- Create the table without the constraints, import with null id values,
update id = nextval..., then alter table.

Am I missing anything?

It's not a huge number of records, so I could perfectly well (and probably
will) just generate a text file of individual INSERT statements. I'm just
asking to make sure my understanding is correct.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 722-0567 voice

Responses

Browse pgsql-general by date

  From Date Subject
Next Message stevegy 2006-10-04 23:43:03 Re: &nbsp;&nbsp;&nbsp;&nbsp;H
Previous Message Jeff Davis 2006-10-04 22:07:23 Re: Run programs within the bin folder