UPDATE in PostgreSQL generates same UUID

user_9040373 0
Viewed 3892

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?

sql-update uuid postgresql sql
2 Answers

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.

user_18632290

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.

user_64643080
Related