How to use a uuid as default ID with postgres?

Question

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.

Answer 1

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.