Re: UUID column as pimrary key?

Yes, the ID is generated before the database is contacted, in my case anyway.

The type of UUID that I want is a hybrid, so I would have to write a stored
procedure and then a trigger upon insert to get the UUID. Not rocket science, I
just have more on my plate than I can handle. So PHP is my 'main thing', so I
quickly wrote it in that. Plus, it makes it more database agnostic.

I don't get next/currval behavior, but I don't think I'll need it.

BTW, Switching from Postgres? Not likely anytime soon. I'd have to be up in the
500M+ rows and be in the data warehousing/map reducing arena before I'd
consider THAT. And there's 'flavors' of Postgres that will do that, anyway.

Dennis Gearon

Signature Warning
----------------
It is always a good idea to learn from your own mistakes. It is usually a better

idea to learn from others’ mistakes, so you do not have to make them yourself.
from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036'

EARTH has a Right To Life,
otherwise we all die.

----- Original Message ----
From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Radosław Smogura <rsmogura(at)softperience(dot)eu>
Cc: Dennis Gearon <gearond(at)sbcglobal(dot)net>; pgsql-general(at)postgresql(dot)org
Sent: Wed, January 5, 2011 2:50:11 AM
Subject: Re: [GENERAL] UUID column as pimrary key?

On 01/05/2011 07:31 PM, Radosław Smogura wrote:

> * you have your id, before executing query, (in contrast to all this
> autoincrement) so you may put it in dependant rows

Do you mean that with a UUID, you don't need to talk to the database at all, you

can generate an ID with no interaction with / involvement with the database at
all? Because other than that, there's not much difference in how you normally
work with them.

With a sequence, you might:

CREATE SEQUENCE x_id_seq;
CREATE TABLE x (
id integer PRIMIARY KEY DEFAULT nextval('x_id_seq'),
y integer
);
INSERT INTO x(y) VALUES (1);

With a uuid, you'd:

CREATE TABLE x (
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
y integer
);
INSERT INTO x(y) VALUES (1);

In either case, you can explicitly call the generator function for seq/uuid -
nextval(seqname) or uuid_generate_v4() respectively - or you can omit the PK
column in your inserts and let the database generate it.

> Personally I prefer pooled incremental id's. Fast, unique, you have Id
> before query - but you need to write "code" by self.

Many libraries / ORMs / etc that interact with Pg will happily take care of this

for you. In fact, I had to fight to convince Hibernate that I *didn't* want it
to increment all my counters in steps of 50.

--
Craig Ringer