Search code examples
sqlpostgresqlsql-updateuuid

UPDATE in PostgreSQL generates same UUID

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?

Solution

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