CODEWITHSUNDEEP
postgresql
codec
codec

Reputation: 8796

How to generate uuids without dashes

I am creating a table with the following table definition

CREATE TABLE auth(
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role                VARCHAR(64)
);

The uuids generated are in the form a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 I know postgres also accepts uuids without hyphens. How can we specify the auto-generated uuids do not have hyphens in it?

Upvotes: 7

Views: 10317

Answers (1)

marcopeg
marcopeg

Reputation: 1228

UPDATE:
Thanks to @a_horse_with_no_name for pointing out that both question and answer are not relevant as Postgres DOES NOT STORE HYPHENS with the UUID type.

Here is a working solution to achieve something close to what you ask:

CREATE TABLE foo (
  ts TIMESTAMP WITH TIME ZONE,
  uuid VARCHAR DEFAULT REPLACE(uuid_generate_v4()::text, '-', '' )
); 

INSERT INTO foo ( ts ) VALUES ( now() );

BUT (and it is a big but) here we convert uuid to a string that means that the index itself will be much more expensive than a number or a real uuid.

In this article you can find a good explanation:
https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439

As far as I know, Postgres' uuid uses hyphens even if you try to remove them:

CREATE TABLE foo (
  ts TIMESTAMP WITH TIME ZONE,
  queue UUID DEFAULT REPLACE(uuid_generate_v4()::text, '-', '' )::uuid,
);

INSERT INTO foo ( ts ) VALUES ( now() );

The example above works just fine in Postgres 9.6, but when we cast back to uuid hyphens are added back.

Upvotes: 3

Related Questions