How to create guid in PostgreSQL

Question

How to create GUID in Windows format in Postgres 9.0+?

I tried function

CREATE or REPLACE FUNCTION public.getguid() RETURNS varchar AS $BODY$  DECLARE    v_seed_value varchar(32);  BEGIN    select      md5(        inet_client_addr()::varchar ||        timeofday() ||        inet_server_addr()::varchar ||        to_hex(inet_client_port())      )    into v_seed_value;     return (substr(v_seed_value,1,8) || '-' ||            substr(v_seed_value,9,4) || '-' ||            substr(v_seed_value,13,4) || '-' ||            substr(v_seed_value,17,4) || '-' ||            substr(v_seed_value,21,12));  END; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; 

from

http://postgresql.1045698.n5.nabble.com/newid-in-postgres-td1879346.html

Tried

select getguid() union all select getguid() 

but it returns same values

"c41121ed-b6fb-c9a6-bc9b-574c82929e7e" "c41121ed-b6fb-c9a6-bc9b-574c82929e7e" 

How to fix this so that unique rows are returned?

Answer 1

PostgreSQL has the uuid-ossp extension which ships with the standard distributions and it has 5 standard algorithms for generating uuids. Note that a guid is the Microsoft version of a uuid, conceptually they are the same thing.

CREATE EXTENSION "uuid-ossp"; 

Then:

SELECT uuid_generate_v4(); 

Note also that, once you installed the extension, PostgreSQL has an actual binary uuid type, with a length of 16 bytes. Working with the binary type is much faster than working with the text equivalent and it takes up less space. If you do need the string version, you can simply cast it to text:

SELECT uuid_generate_v4()::text; 

---

PostgreSQL 13+

You can now use the built-in function gen_random_uuid() to get a version 4 random UUID.