> -----Original Message----- > From: Mike Christensen [mailto:mike@xxxxxxxxxxxxx] > Sent: Wednesday, June 08, 2011 4:26 PM > To: David Johnston; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
