CODEWITHSUNDEEP
sqlpostgresqluuid
Imran
Imran

Reputation: 149

How to generate random UUID per group of records in postgres

HOw can i create random UUID per group of records ?

for ex, how can I create random uuid per name(diff in color) in below dataset in Postgres sql ?

enter image description here

Upvotes: 1

Views: 4301

Answers (2)

leftjoin
leftjoin

Reputation: 38335

Generate UUID for all rows, then use max(id) over(partition by name) to get the same id for group.

select max(id) over (partition by name) as id, name, age
from 
(
select  name, age, md5(random()::text || clock_timestamp()::text)::uuid as id
  from ...
)s

Also can use uuid-ossp for UUID generation like in @GordonLinoff answer

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270311

First, you need uuid-ossp to generate uuids. This is a module you need to add in.

Then it should be pretty simple method would be:

select t.*, name_uuid
from t join
     (select name, uuid_generate_v4() as name_uuid
      from t
      group by name
     ) n
     using (name);

Upvotes: 1

Related Questions