Thread: Generating synthetic keys on copy

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@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:
> 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?

Right.  Instead, specify a column list to the COPY (you are using a PG
version new enough to have column lists in COPY, no?) and it will
execute the default expression for the column(s) not coming from the data
file.

If it is an old version, what I'd do is COPY into a temp table whose
column set matches the data file, and then use INSERT/SELECT to transfer
the data to the permanent table and fill the missing columns.  This
latter is a good answer anytime you need to do extra data massaging
that COPY can't handle.

            regards, tom lane

> Right.  Instead, specify a column list to the COPY (you are using a PG
> version new enough to have column lists in COPY, no?) and it will
> execute the default expression for the column(s) not coming from the data
> file.

Thanks. (Feeling dumb here.) I am using 8.1, however I haven't read the docs
for COPY in a few years, and didn't realize I could specify a column list.
RTFM, duh.


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