5

Currently in the process of migrating to to postgres after the announcement about dropping mongodb, Just noticed that the ID's are just numbers and auto-incremented.

I have tried:

  1. Setting the default ID as a UUID with a lifecycle hook - No effect
  2. Installing bookshelf-uuid

Anyone know how to do this? I very much do not want the ID of my post to be an auto-incremented number.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Sid
  • 846
  • 3
  • 12
  • 25

1 Answers1

6

Auto-incremented ids and uuids are in general a different concept. For uuids as primary key one often uses random values. Due to the large range of values, duplicates are almost impossible.

You can define an auto-generated uuid primary key as follows:

CREATE TABLE my_table (
    id UUID DEFAULT MD5(RANDOM()::TEXT || CLOCK_TIMESTAMP()::TEXT)::UUID PRIMARY KEY,
    … other column definitions …
);

The pgcrypto extension provides a function for creating random uuids as well.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE my_table (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    … other column definitions …
);

With Postgres 13 the creation of the pgcrypto extension for using gen_random_uuid() isn't necessary anymore.

clemens
  • 16,716
  • 11
  • 50
  • 65
  • 3
    As of postgres 13, gen_random_uuid is available without having to install the extension: https://www.postgresql.org/docs/current/functions-uuid.html – Jeremy May 15 '21 at 09:53
  • @Jeremy Thanks for the hint. I had something like that in mind, but was no longer sure which version it was. I have updated my post. – clemens May 15 '21 at 11:06