Обсуждение: 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