1

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?

  • UUIDs are pretty bad for indexes. One suggestion is to append UUID values to a datetime. That will create a right leaning value that will do better in a btree and may avoid the possibility of collisions, depending on the rate of UUID creation. If that's not good, then you could try creating a surrogate identity value and using that as a primary key. – ThatDataGuy Jul 3 at 9:29
3

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.