What happens when an automatically generated UUID primary key collides in Postgres?

If I have a PostgreSQL table with a column defined as follows, where gen_random_uuid() is from extension pgcrypto.

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

Upon each insert where an UUID is not specified, a new UUID (v4) is automatically generated to be set as the primary key. But seeing as UUIDv4 are generated at random, it could (at an astronomically low chance) collide with an existing row's UUID. What happens in this scenario? Does the insert return with a duplicate key error, or is generation repeated until it finds a UUID that does not collide?

No, it does not retry and thus throws an duplicate key error.

You can use the following code to test it yourself:

CREATE OR REPLACE FUNCTION random_uuid() RETURNS uuid AS $
        BEGIN
            IF random() > 0.5 THEN
                RETURN '35d4b5e2-8fb5-4b89-9874-62b572c8fa2b'::uuid;
            ELSE
                RETURN gen_random_uuid();
            END IF;
        END;
$ LANGUAGE plpgsql;

CREATE TABLE test (id uuid default random_uuid() Primary key, date timestamp);

INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());
INSERT INTO test (date) VALUES (now());

If you want to avoid collisions you might want to use a v1 (time based) uuid.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v1();

But this depends on your usecase (do not use that for crypto/security).

StackExchange Database Administrators Q#214083, answer score: 7