UUID with variable length

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

UUID with variable length

Dirk Krautschick
Hi,

I have here a situation with the usage of UUID. Here the database user allows UUIDs with less then 16 byte lengths (please don't ask :-) ).

Of course there are some technical ways to do the filling of the not used bytes but I hope there is a better solution. This UUID is used
as primary Key and for indexing.

CHAR 16 is not preferred because of the risk of mistakenly changing the binary data.

Another option would be e.g. bytea but how is good would it work from a IO an latency perspective.

Or do you have some other ideas how to use a primary key datatype like UUID but with variable length?

Thanks

Dirk
Reply | Threaded
Open this post in threaded view
|

Re: UUID with variable length

Christophe Pettus-2


> On Oct 15, 2020, at 13:49, Dirk Krautschick <[hidden email]> wrote:
> Or do you have some other ideas how to use a primary key datatype like UUID but with variable length?

You're probably best off storing it as a VARCHAR() with a check constraint or constraint trigger that validates it.

--
-- Christophe Pettus
   [hidden email]



Reply | Threaded
Open this post in threaded view
|

Re: UUID with variable length

Stephen Frost
Greetings,

* Christophe Pettus ([hidden email]) wrote:
> > On Oct 15, 2020, at 13:49, Dirk Krautschick <[hidden email]> wrote:
> > Or do you have some other ideas how to use a primary key datatype like UUID but with variable length?
>
> You're probably best off storing it as a VARCHAR() with a check constraint or constraint trigger that validates it.

Surely a bytea would be better and be less overhead than storing it as
text..

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: UUID with variable length

Álvaro Herrera
In reply to this post by Dirk Krautschick
On 2020-Oct-15, Dirk Krautschick wrote:

> Hi,
>
> I have here a situation with the usage of UUID. Here the database user
> allows UUIDs with less then 16 byte lengths (please don't ask :-) ).
>
> Of course there are some technical ways to do the filling of the not
> used bytes but I hope there is a better solution. This UUID is used as
> primary Key and for indexing.

How much shorter than 16 bytes are you expecting your UUIDs to be?  If
they're not short enough, you'll still have to store a lot of padding
space, and considering that you'll need a length indicator if you use
variable length, it does not really sound like you'll save much actual
space.  And you'll definitely be getting a slower datatype, since doing
operations will become more complex.

If this were me, I would see about zeroing out the unused bytes and not
waste a lot of developer time on this.