Thread: UUID with variable length

Hi,

I have here a situation with the usage of UUID. Here the database user allows UUIDs with less then 16 byte lengths
(pleasedon'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.
ThisUUID 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

> On Oct 15, 2020, at 13:49, Dirk Krautschick <Dirk.Krautschick@trivadis.com> 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
   xof@thebuild.com

Greetings,

* Christophe Pettus (xof@thebuild.com) wrote:
> > On Oct 15, 2020, at 13:49, Dirk Krautschick <Dirk.Krautschick@trivadis.com> 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

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.