Thread: GUID function in pgsql?
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 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.
> 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
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
> 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
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.
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