UPDATE in PostgreSQL generates same UUID

Question

I have added new uuid column for one of my tables and installed uuid-ossp extension. Now I want to update all existing records and set value for this new uuid column.

I do not want to use DEFAULT fallback for ADD COLUMN, but rather I want to do it in UPDATE statement, so I have something like this:

UPDATE table_name SET uuid = (SELECT uuid_generate_v4());

but the issue I have is that same UUID is generated for all records.

Is there a way to pass seed value or something to generate function or another way to enforce generated UUIDs to be unique?

Answer 1

You could try modifying the UUID subquery such that it forces/tricks Postgres into generating a new UUID for each record:

UPDATE table_name
SET uuid = uuid_generate_v4()
WHERE uuid IS NOT NULL;

The WHERE clause is just a dummy, but perhaps will result in Postgres calling the UUID function once for each record.

Answer 2

If you call the uuid_generate_v4() function in a subquery, PostgreSQL will assume that the subquery needs to be called only once, since it does not contain any reference to the surrounding query. Consequently, all rows will be updated to the same uuid, which fails.

If you remove the subquery and leave only the function call, the function is called for each row, since it is VOLATILE.