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

Search Discussions

  • Chris Browne at Nov 5, 2010 at 10:24 pm

    "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
  • Tom Lane at Nov 6, 2010 at 3:43 pm

    "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
  • Jasen Betts at Nov 7, 2010 at 10:55 am

    On 2010-11-05, Wm.A.Stafford 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ıʇǝǝɹ⅁

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 5, '10 at 8:23p
activeNov 7, '10 at 10:55a
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase