4

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?

codec
  • 6,576
  • 18
  • 63
  • 115
  • 12
    The UUID data type does not contain dashes. The dashes are only shown when you _display_ the data. Similar to DATEs that don't have a format. – a_horse_with_no_name Nov 21 '16 at 16:19
  • 1
    wow! I am able to query with uuids wihtout hyphens! – codec Nov 21 '16 at 16:22
  • 3
    Which is well documented in the manual: https://www.postgresql.org/docs/current/static/datatype-uuid.html – a_horse_with_no_name Nov 21 '16 at 16:24
  • Postgres supports different '`format` versions for uuid. Beyond that use of the various formats evaluate the same ( ie '`a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11':: uuid = 'a0eebc999c0b4ef8bb6d-6bb9bd380a11::uuid' evaluates as TRUE. But when produced it is always in *Standard Form* with the dashes. See [Documentation](https://www.postgresql.org/docs/current/datatype-uuid.html). to eliminate the dashes cast the uuid column to text then use replace to remove then : replace(::text, '-','') – Belayer Jul 29 '21 at 23:11

1 Answers1

1

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.

marcopeg
  • 826
  • 8
  • 14