Thread: COPY command and serial columns

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

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

"Wm.A.Stafford" <stafford@marine.rutgers.edu> 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?

Nope, sorry, COPY lacks the "READ MY MIND" option.  If the incoming
data doesn't include every column of the table, you have to specify
a column list to tell it which columns the data does include.

Possibly you could make your code robust against table alterations by
having it look into the system catalogs to get the current column
list.  Then it could leave out the column name(s) it knows a-priori
aren't in the data.

            regards, tom lane

On 2010-11-05, Wm.A.Stafford <stafford@marine.rutgers.edu> wrote:
> 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?

a: drop the column, load the data, re-add the column.

b: load the data into a temporary first.

c: otherwise "no"


--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁