Re: 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