Re: Converting uuid primary key column to serial int

> -----Original Message-----
> From: Mike Christensen [mailto:mike(at)kitchenpc(dot)com]
> Sent: Wednesday, June 08, 2011 4:26 PM
> To: David Johnston; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Converting uuid primary key column to serial int

> I'm assuming I can still have a "Serial" column that is NOT a primary key,
and
> it'll incremement just the same as I add rows? If that's the case, I
think that's
> a superior approach..
>
> BTW, this table is too small to worry about disk space of UUIDs and/or
> perhaps any sort of performance benefits to using int over uuid (if there
are
> any)..
>
> Mike

" CREATE TABLE t ( field serial ); " is simply short-hand for " CREATE TABLE
t (field integer DEFAULT nextval('t_seq') ); " where the sequence "t_seq" is
automatically created and linked to the table.

Nothing more and nothing less. Whether you add NOT NULL or, by extension,
PRIMARY KEY, to the field as well as the "serial" datatype depends on
whether you want to have those other properties.

Have you considered giving the row for "eggs" the PK of "eggs"? You did say
you have multiple thousands of records but neither the UUID or the integer
is going to stop you from then having 2+ records with "eggs" as the name.
If you want to see how many recipes use "eggs" what would you do to make
sure you are not missing any? Even if you decide to keep the UUID and/or
Integer as UNIQUE indices you should try and have something in the data
itself that can be defined as UNIQUE. Since you are dealing with discreet
items, without any kind of time property, it should be possible to do so.

From an implementation perspective you will want to create the sequence and
all using "serial" but allow "NULL" for the field. Once you've assigned all
the existing records an ID (probably via the row() window function) you can
setup the sequence to begin with the next available number. See docs for
syntax.

David J.