6

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
  • 7,978
  • 26
  • 71
  • 127
  • 13
    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. –  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 –  Nov 21 '16 at 16:24
  • 1
    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

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.

marcopeg
  • 1,028
  • 10
  • 15
  • 4
    The hyphens are **not** stored within the column. They are only generated when you _display_ the value. –  Dec 17 '19 at 08:19