Re: Converting uuid primary key column to serial int

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Mike Christensen
> Sent: Wednesday, June 08, 2011 2:57 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Converting uuid primary key column to serial int

> for each ingredient are kinda a hassle to manage though, and I'm thinking
in
> this case I might just want to use a 32bit integer for the primary key..

> 2) Once I do this, I obviously need to fix up all the foreign keys and
convert
> them from the old UUIDs to the new generated numeric value.
> Is there an easy way to manage this sort of thing? I'm thinking about
keeping
> the old UUID as a non-PK column to use as a "mapping" as I migrate the
rest
> of the schema over. Is that a good approach?
>

What specific hassles are you encountering? Seems like a lot of effort to
go through to make the data less-unique. Since you already have the field
setup for PK/FK use try to leave that alone and just add a secondary
identifier that you use for queries. Like you said in #2 leave the UUID in
place as a non-PK (but still unique/not-null) field and leave all the
foreign keys in place as well. Add your "integer identity" column as the
new Primary Key and use that field when you want to specify a record. Then,
for those cases where you have or want to use the integer PK but the field
being compared is the UUID you can write a simple function to return the
UUID associated with the given integer.

Otherwise you probably should just add the integer field to EVERY TABLE and
establish relational links. Add the field as null-able, perform the update
using the UUID, change to not null, add FK constraint. As you then attempt
to remove the UUID field from the tables the system will tell you where
different function and views are relying upon the UUID field and you can
change the object to use the new integer field instead.

The main risk really is in the application since the database will not be
able to enforce consistency.

Serial is implemented via "DEFAULT" and sequences; and if you dump/restore a
sequence it is done consistently.

David J.