Thread: GUID function in pgsql?

GUID function in pgsql?

From
Date:
hi list
 
i'm looking for a GUID function in pgsql to ease migration from mssql. several tools rely on a primary key that consists of a guid column. unfortunately, pgsql does not seem to provide such a datatype, and much worse i wasn't even able to find a similar pgsql function that generates a GUID.
 
the list archives found some discussions about how a guid could be implemented - unfortunately without giving a final solution. is there really no GUID creation function? if so, could it be requested for a (hopefully not so future) pgsql release?
 
- thomas

Re: GUID function in pgsql?

From
Bruno Wolff III
Date:
On Thu, Dec 15, 2005 at 05:59:30 +0100,
  me@alternize.com wrote:
> hi list
>
> i'm looking for a GUID function in pgsql to ease migration from mssql. several tools rely on a primary key that
consistsof a guid column. unfortunately, pgsql does not seem to provide such a datatype, and much worse i wasn't even
ableto find a similar pgsql function that generates a GUID. 
>
> the list archives found some discussions about how a guid could be implemented - unfortunately without giving a final
solution.is there really no GUID creation function? if so, could it be requested for a (hopefully not so future) pgsql
release? 
>
> - thomas

Normally you want to use sequences to do this. If you need something more
global you could combine this with some unique ID for your database.

Re: GUID function in pgsql?

From
Date:
> Normally you want to use sequences to do this. If you need something more
> global you could combine this with some unique ID for your database.

normally i would use serials, of course. but not when the tools expect a
guid (i.e. something like 'a4180365-b4b5-4013-bd7b-7b6a386eb343'). and as
i'm only in control of the DBMS and not the tools itself, i will need a guid
somehow...

- thomas



Re: GUID function in pgsql?

From
Norberto Meijome
Date:
me@alternize.com wrote:

>> Normally you want to use sequences to do this. If you need something
>> more
>> global you could combine this with some unique ID for your database.
>
>
> normally i would use serials, of course. but not when the tools expect
> a guid (i.e. something like 'a4180365-b4b5-4013-bd7b-7b6a386eb343').
> and as i'm only in control of the DBMS and not the tools itself, i
> will need a guid somehow...
>
Bruno's advise is still valid. If you google around a bit, you'll across
explanations like the one in
http://blogs.msdn.com/oldnewthing/archive/2004/02/11/71307.aspx

on how MS-GUIDs are created. If fitting to your GUID-loving-app is the
key here, you can easily write a function in C or other language that
when called it returns a GUID in the exact format you need.
Somethign like
substr(random(n,m,sha1(rand(ms_since_epoch())))-[unique ID generated
from your MAC address]

or something like that - you get the picture.
B


Re: GUID function in pgsql?

From
Date:
> Bruno's advise is still valid. If you google around a bit, you'll across
> explanations like the one in
> http://blogs.msdn.com/oldnewthing/archive/2004/02/11/71307.aspx

i know how a guid is programmatically generated. but that's not the point
here. i'm wondering why pgsql does not come with a predefined function that
already provides this functionality. it would ease the move from other dbms
systems quite alot. it's not about arguing whether or not encoding a
mac-adress into the guid is a good thing. it's more about what people need
when they want to favor pgsql over mssql and migrate existing systems into
the new environement...

Aleksandar Dezelin suggested this project:
> http://gborg.postgresql.org/project/pguuid/projdisplay.php

this seems to provide just about what i would need. the project is not yet
ported to the win32 pgsql version tho, but its author is currently porting
it. would be nice to see it in the next pgsql version's contrib ;-)

- thomas



Re: GUID function in pgsql?

From
Bruno Wolff III
Date:
On Thu, Dec 15, 2005 at 13:04:47 +0100,
  me@alternize.com wrote:
> >Bruno's advise is still valid. If you google around a bit, you'll across
> >explanations like the one in
> >http://blogs.msdn.com/oldnewthing/archive/2004/02/11/71307.aspx
>
> i know how a guid is programmatically generated. but that's not the point
> here. i'm wondering why pgsql does not come with a predefined function that
> already provides this functionality. it would ease the move from other dbms
> systems quite alot. it's not about arguing whether or not encoding a
> mac-adress into the guid is a good thing. it's more about what people need
> when they want to favor pgsql over mssql and migrate existing systems into
> the new environement...

Probably because it is not very general and it is easy to generate a string
matching whatever format your app expects without much work.

Re: GUID function in pgsql?

From
Chris Browne
Date:
sys@meijome.net (Norberto Meijome) writes:

> me@alternize.com wrote:
>
>>> Normally you want to use sequences to do this. If you need
>>> something more
>>> global you could combine this with some unique ID for your database.
>>
>>
>> normally i would use serials, of course. but not when the tools
>> expect a guid (i.e. something like
>> 'a4180365-b4b5-4013-bd7b-7b6a386eb343'). and as i'm only in control
>> of the DBMS and not the tools itself, i will need a guid somehow...
>>
> Bruno's advise is still valid. If you google around a bit, you'll
> across explanations like the one in
> http://blogs.msdn.com/oldnewthing/archive/2004/02/11/71307.aspx
>
> on how MS-GUIDs are created. If fitting to your GUID-loving-app is the
> key here, you can easily write a function in C or other language that
> when called it returns a GUID in the exact format you need.
> Somethign like
> substr(random(n,m,sha1(rand(ms_since_epoch())))-[unique ID generated
> from your MAC address]
>
> or something like that - you get the picture.

More appropriate is to link out to the C function/library on your
platform that generates GUIDs.

Linux has one (libuuid); others probably borrow it, or could.

And it seems stupid to me to generate something that is
"handwavy-vaguely similar to GUIDs" rather than doing it *properly*
as per the specs (RFC 4122).

The costs of dealing with "handwavy-similar wasn't good enough" bugs
are likely to be way higher than the costs of reading and following
RFC 4122 in the first place.

There is, after all, a relevant project at gBorg...
<http://gborg.postgresql.org/project/pguuid/projdisplay.php>
--
"cbbrowne","@","ntlug.org"
http://www3.sympatico.ca/cbbrowne/rdbms.html
"Not to oppose error, is to approve of it, and  not to defend truth is
to suppress it; not to confound evil men when we can do it, is no less
a sin than to encourage them."  -- Pope Felix III, ca. 490