Hi all - For most of my database I use UUIDs as primary keys because, well, I just like it better and like being able to generate a key in the middle tier when I create new data. However, I have one table that has a very fixed and immutable set of data with a few thousand ingredients in it. This set very rarely changes (in fact when I change it, I have to restart the whole website).. The UUIDs 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.. Almost every other table in the database has FK relationships with these ingredients, and there's all sorts of JOINs against it. So, making this change is gonna be fairly costly and require all sorts of schema changes to everything. I have a few related questions on this topic.. 1) What's the best way to convert an existing table from UUIDs to a serial INT column? I want to have Postgres just assign sequential numbers starting at 1 to all the rows. 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? 3) I'm also worried about backup/restores/replication. I often add new ingredients to my production database, and every so often backup this database and restore it to my dev box so I can work with production data. I need to make sure all these ingredient IDs stay constant, just as the UUIDs did. Does pg_dump generate SQL that will "re-create" the existing keys and set the identity watermark correctly? If someday I have replication enabled, do the same IDs get replicated consistantly? Any other "gotchas" here? Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
