Re: UUID as primary key

# vmaverick@pgp.com / 2006-08-21 21:16:14 -0700:
> I'm considering using a UUID as a primary / foreign key for my schema,
> to help ensure portability of data in a multi-master context.  Does
> anyone have experience with this?
>
> There's a project on Gborg (pguuid) to create a "native" UUID type, but
> it looks stagnant (and I'd prefer to use PostgreSQL out of the box, if I
> can).  So I'm considering three possible representations:
>
> * VARCHAR(36) or CHAR(36) containing the standard textual encoding
> * NUMERIC(40,0) containing the 128-bit binary version of the UUID,
> considered as an integer
> * A pair of BIGINT columns, containing the two 64-bit halves of the
> binary UUID, handled together as a two-column key.
>
> Would any of these give reasonable performance (for joins of large
> tables), compared to int4 IDs?  Is any of them clearly any better or
> worse than the others?

    Ralf Engelschall's OSSP uuid looks very good. Written in C with
    interfaces into PostgreSQL, PHP and C++ (classes wrapping the C
    structures and functions).

    http://www.ossp.org/pkg/lib/uuid/

    You should be able to e. g.

    CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY);

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991