Re: Using varchar primary keys.

On the topic of 'natural' versus 'synthetic' primary keys, I am generally
in the camp that an extra ID field won't cost you too much, and while one
may not need it for a simple table (i.e. id, name) one might add any number
of columns later, and you'll be glad to have it.

I am, however, against using sequences (or serial integers in Postgres) for
reasons of scaling and replication across multiple copies of a database
running on different servers.

My preferred method is to give every table an ID column of UUID type and
generate a UUID using the uuid-ossp contrib module. This also prevents
someone not familiar with the database design from using an ID somewhere
they should not (as is possible with natural PKs) or treating the ID as an
integer, not an identifier (as is all too common with serial integers).

On Mon, Apr 1, 2013 at 9:35 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Mon, Apr 1, 2013 at 1:22 AM, Jasen Betts <jasen(at)xnet(dot)co(dot)nz> wrote:
> > On Mon, Apr 01, 2013 at 07:08:15PM +1300, Tim Uckun wrote:
> >> >
> >> > how about using an enum instead of this table?
> >> >
> >> >
> >> That's an interesting idea. Are enums mutable?
> >
> >
> > since 9.1 you can add values.
> >
> > http://www.postgresql.org/docs/9.1/static/sql-altertype.html
>
> It's an interesting idea, but I don't think enums are designed to act
> as a primary key except in cases where the data is basically static
> and is relatively small. For starters, any manipulation of the enum
> requires a lock.
>
> enums can be a real life saver when you need custom ordering built
> into a string, especially if that ordering is floated over a composite
> index.
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GIT d+ s: a-- C++++(++) UL+++ P++++$ L+++>++++ E- W+ N o? K w--- O-
M- V? PS+++ PE(-) Y+ PGP->+++ t+>++ 5+++++ X(+) R>+ tv b+
DI++ D++>+++ G+ e* h! !r y**
------END GEEK CODE BLOCK------