Re: Change primary key in Postgres 7.3?

Michael Hannon wrote:

> Greetings. We're running Postgres 7.3 on an Intel linux box (Redhat
> Enterprise server, version 3.0). We find ourselves in an awkward
> position: we have a database of attributes relating to students that
> uses as its primary key the ID number of the student. This is awkward
> for the following reasons.
>
> Our university used to use social-security numbers for student ID's.
> They stopped doing that a few years ago, but didn't force the change on
> existing students. Recently they've made a forced, retroactive change
> such that ALL students, past and present, now have a student ID that's
> not related to social-security number.
>
> I think this a well-justified change, but, unfortunately for us, it
> makes many of the primary keys in our database invalid. This problem is
> compounded by the fact that the programmer that set up our Postgres
> databases has moved on to another job.
>
> Our current programmer would like to start from scratch, redefine the
> schema, rebuild the database, etc. Unfortunately, there are a number of
> high-profile applications that depend on the database, and many of them
> would surely get broken by this kind of transition.
>
> We expect that we WILL eventually rebuild the database, but right now
> we're looking for a quick fix. Our current programmer tells me that he
> can't find a way to simply change the primary key "in place" in Postgres.
>
> Is there a way to do this?
>
> Thanks.
>
> - Mike

It can be done (see other posts), but I suspect that your programmer has
a good reason to be reluctant.

<unwanted_advice>

Let your programmer start from scratch on a new server. Once he has it
set up how he wants it, and has tested it against the other
applications, make the switch. If it doesn't work right away, you can
always switch back.

</unwanted_advice>

John