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

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

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

> 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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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/ar.../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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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/ar.../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/...rojdisplay.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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/ar.../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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/ar.../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