>> 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.
Yea, there's already a unique index on the ingredient name, so there
can only be 1 "eggs" row.. I'm not too sure on using the name as the
primary key..
The serial implementation makes sense.. If that's all that it does, I
should be able to just update all rows and set the numeric value to
nextval('t_seq') directly to order all my existing rows, then set the
column to NOT NULL when I'm done, and set the sequence to the next
available number.. Worst case I'll just write a little function that
loops through my rows and numbers them all. I'll checkout the docs on
this, thanks!
Mike