CODEWITHSUNDEEP
sqlpostgresqlfunctionuuid
Tsar Bomba
Tsar Bomba

Reputation: 1106

Postgresql equivalent to this UUID-based function

I'm in the process of evaluating PostgreSQL for new projects, which we would have otherwise used MySql for. I generate these types of IDs in MySql, which is a flipped-around V1 GUID:

create function get_uuid() returns binary(16)
begin
    return (
        select unhex(concat(
            substr(uuid(), 15, 4),
            substr(uuid(), 10, 4),
            substr(uuid(), 1, 8),
            substr(uuid(), 20, 4),
            substr(uuid(), 25))) as new_id
        );
end;

I would then use the mysql hex() and unhex() functions to convert them to/from binary(16), so as to halve the amount of storage the field requires.

All of this is documented here, if you're curious:

https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

BUT...what I'm looking for, is the equivalent function for Postgres. I'm 100% new to it, as of today.

Upvotes: 1

Views: 1148

Answers (1)

user330315
user330315

Reputation:

This hack isn't needed with Postgres. It has a native data type uuid which only takes 16 bytes to store a UUID value.

Upvotes: 4

Related Questions