Opinion wanted on UUID/GUID datatype output formats.

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