GUIDs

[David Wheeler]

Hi All,

I want to use GUIDs for object IDs in my application. This means that
they would be used for primary keys in PostgreSQL, and there would of
course be FKs pointing to them. A GUID is 128 bits, and can be in the
following possible formats:

* 16 byte binary (but then I'd have to convert it to hex in my app)
* 32 byte string (e.g., '4162F7121DD211 B2B17EC09EFE1DC 403')
* hex string (e.g., 0x3271839C163D1 1D891F785398CC7 932E)
* Base 64 string (e.g., 'MnIAdBY9EdiR94 U5jMeTLg==')

It looks like the hex option would be the best option, but there's no
native hex format in PostgreSQL. Anyone have suggestions on what the
best approach might be? I can't convert it to a number, really, because
128 bit numbers aren't too portable).

Please Cc any replies to me, as I'm not subscribed to the mail list.

Many TIA,

David

--
David Wheeler AIM: dwTheory
da***@kineticod e.com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Th****@jabber.o rg
Kineticode. Setting knowledge in motion.[sm]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Peter Eisentraut]

David Wheeler writes:

It looks like the hex option would be the best option, but there's no
native hex format in PostgreSQL. Anyone have suggestions on what the
best approach might be? I can't convert it to a number, really, because
128 bit numbers aren't too portable).

Use bytea. It stores bytes and allows the conversion into several output
formats.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[David Wheeler]

On Thursday, November 13, 2003, at 08:04 PM, Peter Eisentraut wrote:

Use bytea. It stores bytes and allows the conversion into several
output
formats.

bytea with the binary or the hex? And isn't it a bit of a waste to add
the extra 4 bytes when I'll only ever need 16?

Thanks,

David

--
David Wheeler AIM: dwTheory
da***@kineticod e.com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Th****@jabber.o rg
Kineticode. Setting knowledge in motion.[sm]
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Peter Eisentraut]

David Wheeler writes:

bytea with the binary or the hex?
Binary or hex what?
And isn't it a bit of a waste to add the extra 4 bytes when I'll only
ever need 16?

Sure, but if you want to avoid that, you'll have to implement your own
data type. Actually, I think someone has already done that for GUID. If
you search the archives you might find out about it.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[David Wheeler]

On Thursday, November 13, 2003, at 08:30 PM, Peter Eisentraut wrote:

Bbytea with the binary or the hex?

Binary or hex what?

Representation of the UUID.

And isn't it a bit of a waste to add the extra 4 bytes when I'll only
ever need 16?

Sure, but if you want to avoid that, you'll have to implement your own
data type. Actually, I think someone has already done that for GUID.
If
you search the archives you might find out about it.

Sure enough. Josh just mentioned this to me:

http://gborg.postgresql.org/project/...rojdisplay.php

Looks like 1.0.0 was released June 17, 2003. I wonder how robust it is?

Cheers,

David

--
David Wheeler AIM: dwTheory
da***@kineticod e.com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Th****@jabber.o rg
Kineticode. Setting knowledge in motion.[sm]
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Bruno Wolff III]

On Thu, Nov 13, 2003 at 19:57:33 -0500,
David Wheeler <da***@kinetico de.com> wrote:

Hi All,

I want to use GUIDs for object IDs in my application. This means that
they would be used for primary keys in PostgreSQL, and there would of
course be FKs pointing to them. A GUID is 128 bits, and can be in the
following possible formats:

Couldn't you use numeric? That should be fairly portable.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[David Wheeler]

On Friday, November 14, 2003, at 12:04 AM, Bruno Wolff III wrote:

Couldn't you use numeric? That should be fairly portable.

Not really. The GUID is 128 bits, which doesn't work so well on 32-bit
systems.

Regards,

David

--
David Wheeler AIM: dwTheory
da***@kineticod e.com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Th****@jabber.o rg
Kineticode. Setting knowledge in motion.[sm]
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Doug McNaught]

David Wheeler <da***@kinetico de.com> writes:

On Friday, November 14, 2003, at 12:04 AM, Bruno Wolff III wrote:

Couldn't you use numeric? That should be fairly portable.

Not really. The GUID is 128 bits, which doesn't work so well on 32-bit
systems.

NUMERIC is an arbitrary-precision integer, not a machine word.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[David Wheeler]

On Friday, November 14, 2003, at 06:18 PM, Doug McNaught wrote:

NUMERIC is an arbitrary-precision integer, not a machine word

Yeah, but that doesn't help me in Perl.

Regards,

David

--
David Wheeler AIM: dwTheory
da***@kineticod e.com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Th****@jabber.o rg
Kineticode. Setting knowledge in motion.[sm]
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Bruno Wolff III]

On Fri, Nov 14, 2003 at 17:52:41 -0500,
David Wheeler <da***@kinetico de.com> wrote:

On Friday, November 14, 2003, at 12:04 AM, Bruno Wolff III wrote:

Couldn't you use numeric? That should be fairly portable.

Not really. The GUID is 128 bits, which doesn't work so well on 32-bit
systems.

On postgres numeric isn't limited to what can be represented in 32 bits.
I am not sure about other systems, but I would expect it to not be limited
to just 32 bits on them either.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster