Thread: Opinion wanted on UUID/GUID datatype output formats.
Folks,
I would like to have your opinion on the following:
At this moment we (almost) have a uuid/guid datatype.
As suggested in earlier discussion we provide a raw/plain output of the
uuid type:
devdb=# select * from tbluuid; pk |
----------------------------------+6b13c5a1afb4dcf5ce8f8b4656b6c93c |01e40a79b55b6e226bffb577e960453d |
(2 rows)
I was wondering if we want to have a formatting function to be able to
provide other common formats of the uuid/guid?
something like:
select format_uuid(mypk,'format2') from tbluuid;
and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c
or
select format_uuid(mypk,'format3') from tbluuid;
and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
(which would be MSSQL compatible)
Do we want such a function added to the core or we let the application
handle the formatting if ever needed.
What do the PostgreSQL masters think? :)
Regards,
Gevik.
> select format_uuid(mypk,'format2') from tbluuid; > and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c How about instead of fixed formats, you allow a format string using the diverse parts of the GUID a la time formatting functions ? Then everybody can format it as they want. Just an idea. Cheers, Csaba.
On 9/14/06, Gevik Babakhani <pgdev@xs4all.nl> wrote: > At this moment we (almost) have a uuid/guid datatype. > As suggested in earlier discussion we provide a raw/plain output of the > uuid type: > > devdb=# select * from tbluuid; > pk | > ----------------------------------+ > 6b13c5a1afb4dcf5ce8f8b4656b6c93c | > 01e40a79b55b6e226bffb577e960453d | > (2 rows) Which is a Good Format. > I was wondering if we want to have a formatting function to be able to > provide other common formats of the uuid/guid? > > something like: > > select format_uuid(mypk,'format2') from tbluuid; > and then get: 6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c Ehm, I would strongly suggest rather something similar to to_char() family of date-and-other-stuff formatting function, in the above example: SELECT to_char(mypk,'NNNNNNNN-NNNN-NNNN-NNNN-NNNNNNNNNNNN') FROM tbluuid; ...or maybe some shorter syntax, like '8N-4N-4N-4N-12N'). This way it gains both flexibility (ANY format user wants is possible, say using slashes as separator (great for hash-like filename generator) and readability (no need to look for 'formatN' definition). Regards, Dawid
Gevik,
> select format_uuid(mypk,'format3') from tbluuid;
> and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
> (which would be MSSQL compatible)
> What do the PostgreSQL masters think? :)
There are no masters here. Except in replication.
I think that we should have a formatting function, but it should be developer
defined rather than pre-set, like to_char is. For example, instead of:
> select format_uuid(mypk,'format3') from tbluuid;
> and then get: {6b13c5a1-afb4-dcf5-ce8f-8b4656b6c93c}
Have:
select format_uuid(mypk,'HHHHHH-HHHH-HHHH-HHHH-HHHHHHHHHHH')
... to get the same result. Or you could even support regexes:
select format_uuid(mypk,'[0-9a-f]{6}-[0-9a-f]{6}-[0-9a-f]{6}-[0-9a-f]{6}')
... but something which allows the definition of "ad-hoc" formating masks so
that we can cover compatibility with products of which we're not yet aware.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
Gevik Babakhani wrote: > As suggested in earlier discussion we provide a raw/plain output of > the uuid type: > > devdb=# select * from tbluuid; > pk | > ----------------------------------+ > 6b13c5a1afb4dcf5ce8f8b4656b6c93c | > 01e40a79b55b6e226bffb577e960453d | > (2 rows) The UUID standards define a single perfectly clear format, and the one you show is not it. > I was wondering if we want to have a formatting function to be able > to provide other common formats of the uuid/guid? If you stick to the standard format, I don't think that will be necessary. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On Thu, Sep 14, 2006 at 11:32:09PM +0200, Peter Eisentraut wrote:
> Gevik Babakhani wrote:
> > As suggested in earlier discussion we provide a raw/plain output of
> > the uuid type:
> >
> > devdb=# select * from tbluuid;
> > pk |
> > ----------------------------------+
> > 6b13c5a1afb4dcf5ce8f8b4656b6c93c |
> > 01e40a79b55b6e226bffb577e960453d |
> > (2 rows)
>
> The UUID standards define a single perfectly clear format, and the one
> you show is not it.
>
> > I was wondering if we want to have a formatting function to be able
> > to provide other common formats of the uuid/guid?
>
> If you stick to the standard format, I don't think that will be
> necessary.
+1. For people that care about the non-standard MSSQL format, they can
easily create their own function that will wrap it in {}.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>>> devdb=# select * from tbluuid;
>>> pk |
>>> ----------------------------------+
>>> 6b13c5a1afb4dcf5ce8f8b4656b6c93c |
>>> 01e40a79b55b6e226bffb577e960453d |
>>> (2 rows)
>> The UUID standards define a single perfectly clear format, and the one
>> you show is not it.
>>
>>> I was wondering if we want to have a formatting function to be able
>>> to provide other common formats of the uuid/guid?
>> If you stick to the standard format, I don't think that will be
>> necessary.
>
> +1. For people that care about the non-standard MSSQL format, they can
> easily create their own function that will wrap it in {}.
Having been reading through this thread, I was about to make the above
points, but was glad to see that I was beaten to it.
The dashless format is neither standards compliant nor compatible with
other databases that have uuid functions (notably MS SQL Server and
MySQL), nor with microsoft tools where they're used frequently.
(ignoring the {} wrapping stuff which is trivial).
If we add a UUID type to core, I think that a vast majority of the
people who are going to want to use it out there will be expecting the
standard format with dashes. And asking them to put a formatting
function into every query is beyond horrific.
If we want a general raw hex type then let's call it something else,
because calling it UUID will just confuse people. Everyone else follows
the standard on this; we should too.
Tom
> The dashless format is neither standards compliant nor compatible with
> other databases that have uuid functions (notably MS SQL Server and
> MySQL), nor with microsoft tools where they're used frequently.
> (ignoring the {} wrapping stuff which is trivial).
>
> If we add a UUID type to core, I think that a vast majority of the
> people who are going to want to use it out there will be expecting the
> standard format with dashes. And asking them to put a formatting
> function into every query is beyond horrific.
>
> If we want a general raw hex type then let's call it something else,
> because calling it UUID will just confuse people. Everyone else follows
> the standard on this; we should too.
Agreed to all above. The formatting issues are all handled in the patch.
Regards,
Gevik