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?
Best Answer
No, it does not retry and thus throws an duplicate key error.
You can use the following code to test it yourself:
If you want to avoid collisions you might want to use a v1 (time based) uuid.
But this depends on your usecase (do not use that for crypto/security).