postgreSQL uuid generation

At some point in the past, the uuid_generate_* functions were erroneously marked as IMMUTABLE, which would result in the behavior you show. This has been fixed in all the latest minor versions, but you have to re-run the installation script (uuid-ossp.sql) to get the updated function definitions. (You can also look into the installation script to verify that you have an up-to-date version. The functions should be marked VOLATILE.)

Within a given transaction, the function uuid_generate_v4() returns the same value.

When statements are grouped together and run as "one command", there is one transaction, so every call to uuid_generate_v4() will return the same value.

The two ways to "fix" this are:

1. Make separate database calls every time you use the function (this is easiest)
2. Use a non-auto commit connection where you control the transactions and separate each usage within a BEGIN; COMMIT pair (this is a hassle - don't do this unless you have to)

To avoid duplicates you can use generation like this:

select md5(random()::text || clock_timestamp()::text)::uuid AS new_id, id from table;

But, be careful: this generates UUID but it is not UUIDv4. See more: Generating a UUID in Postgres for Insert statement?