Re: COPY command and serial columns

stafford@marine.rutgers.edu ("Wm.A.Stafford") writes:
> I am trying to use COPY to load some data.  I am not using a list of
> column names in the COPY command.  The table I am loading has 80
> character varying columns and a primary key defined as: id serial
> PRIMARY KEY
>
> The id field is not in the data being loaded so COPY gives the message:
> ERROR:  missing data for column "id"
>
> I don't want to use a column list because this table is subject to a
> lot of change and a column list would be yet another possible point of
> failure if it has to be changed to keep up with table changes.
>
> Is there anyway to load this data without specifying a column list?
>
> Thanks for any help or advice,
> -=beeky

I'd consider "point of failure" to be something of a *feature* of a
column list...

After all, if the structure isn't stable, then succeeding at putting the
data into the wrong places could turn out rather badly, no?

I'd think there would be a "win" in keeping the structure of the COPY
fixed, as much as possible, in which case, again, having change be a
point of failure has some value.

If you really, really, really want to pretend it's not got a fixed
schema, then you might use COPY to load the data into a temporary-ish
table of all text fields that doesn't impose any structure, and use that
as a "staging area" to load the data to where it's *really* supposed to
go, transforming columns and such.

I used that technique, once upon a time, when loading oddly-formatted
data that needed to get converted from some outsider's legacy form to
the rather stricter form we use in our apps.  It tended to involve days
of human time being spent picking out errors.  :-)

I'd then do further transformations (in the ugliest such case, using a
series of VIEWs) to get the data into a more acceptable form.

But I still keep coming back to the point that complaining about wrong
data is a mighty useful protection...
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
"[In 'Doctor' mode],  I spent a good ten minutes  telling Emacs what I
thought of it.   (The response was, 'Perhaps you could  try to be less
abusive.')"  -- Matt Welsh