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?

Doyenne

Is there any reason you don't use the already supplied functions? postgresql.org/docs/current/static/uuid-ossp.html – Cracking

That UUID generator uses values that don't change during a transaction to generate UUIDs, so it generates the same one. It's a poor implementation; use the official functions that a_horse_with_no_name linked to. – Kono

Could be a Windows issue, lack of precision: postgresql.org/message-id/[email protected] – Copy

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.

Public

I tried CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA public but got error could not open extension control file "/usr/pgsql-9.3/share/extension/uuid-ossp.control": No such file or directory . How to fix ? Using standard distro in "PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" – Doyenne

That is odd because the module should be included in the standard distribution at its standard location. You could try to CREATE EXTENSION pgcrypto which creates the function gen_random_uuid() that returns a v4 uuid. – Public

I tried but got error could not open extension control file "/usr/pgsql-9.3/share/extension/pgcrypto.control": No such file or directory Maybe something needs installed in server ? – Doyenne

Your installation is quite obviously not standard. Try to locate the *.control files on your file system with the find command and then move the entire directory contents where you find these files over to /usr/pgsql-9.3/share/extension. – Public

There is only one control file /usr/pgsql-9.3/share/extension/plpgsql.control and it seems to be in proper place. Repo list contains line baseurl=http://yum.postgresql.org/9.3/redhat/rhel-$releasever-$basearch. It looks like this is used for installation. Maybe some yum command can used to add other extensions ? – Doyenne

In Windows server ERROR: could not load library "C:/Program Files/PostgreSQL/9.3/lib/uuid-ossp.dll": The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail. Error occurs for this command – Doyenne

@Doyenne I had to run yum install postgresql93-contrib to get the uuid-ossp extension installed on a CentOS 6.7 machine. From what I've found in the Postgres documentation pgcrypto can generate UUIDs from 9.4 or later. – Salverform

Is there a difference between uuid_generate_v4() and gen_random_uuid()? – Harrold

@DanEsparza The documentation indicates that both functions produce a V4 uuid so they should be identical (pun intended) in terms of what they do. The big difference is that for gen_random_uuid() you do not have to install an extension. – Public

Answer 2

CREATE or REPLACE FUNCTION public.getguid() RETURNS uuid AS $BODY$ 
DECLARE
  v_seed_value uuid; 
begin
 select cast(g.guid as uuid)
 into v_seed_value
 from (
SELECT uuid_in(overlay(overlay(md5(random()::text || ':' || random()::text) placing '4' from 13) placing to_hex(floor(random()*(11-8+1) + 8)::int)::text from 17)::cstring)  guid
) g;
  return v_seed_value;
END; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

Crossopterygian