Re: Generating synthetic keys on copy

Scott Ribe <scott_ribe(at)killerbytes(dot)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