<para>

The data type <type>uuid</type> stores Universally Unique Identifiers

(UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc4122">RFC4122</ulink>,

(UUID) as defined by <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC9562</ulink>,

ISO/IEC 9834-8:2005, and related standards.

(Some systems refer to this data type as a globally unique identifier, or

GUID,<indexterm><primary>GUID</primary></indexterm> instead.) This

<primary>gen_random_uuid</primary>

</indexterm>

<indexterm>

<primary>uuidv4</primary>

</indexterm>

<indexterm>

<primary>uuidv7</primary>

</indexterm>

<indexterm>

<primary>uuid_extract_timestamp</primary>

</indexterm>

</indexterm>

<para>

<productname>PostgreSQL</productname> includesone function to generate a UUID:

<productname>PostgreSQL</productname> includesseveral functions to generate a UUID.

<synopsis>

<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>

<function>uuidv4</function> () <returnvalue>uuid</returnvalue>

</synopsis>

These functions return a version 4 (random) UUID.

<synopsis>

<function>uuidv7</function> (<optional> <parameter>shift</parameter> <type>interval</type> </optional>) <returnvalue>uuid</returnvalue>

</synopsis>

This function returns a version 4 (random) UUID. This is the most commonly

used type of UUID and is appropriate for most applications.

This function returns a version 7 UUID (UNIX timestamp with millisecond

precision + sub-millisecond timestamp + random). This function can accept

optional <parameter>shift</parameter> parameter of type <type>interval</type>

which shift internal timestamp by the given interval.

</para>

<para>

<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamp with time zone</returnvalue>

</synopsis>

This function extracts a <type>timestamp with time zone</type> from UUID

version 1. For other versions, this function returns null. Note that the

extracted timestamp is not necessarily exactly equal to the time the UUID

was generated; this depends on the implementation that generated the UUID.

version 1 and 7. For other versions, this function returns null. Note that

the extracted timestamp is not necessarily exactly equal to the time the

UUID was generated; this depends on the implementation that generated the

UUID.

</para>

<para>

<synopsis>

<function>uuid_extract_version</function> (uuid) <returnvalue>smallint</returnvalue>

</synopsis>

This function extracts the version from a UUID of the variant described by

<ulink url="https://datatracker.ietf.org/doc/html/rfc4122">RFC4122</ulink>. For

<ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC9562</ulink>. For

other variants, this function returns null. For example, for a UUID

generated by <function>gen_random_uuid</function>, this function will

return 4.

#defineNS_PER_SINT64CONST(1000000000)

#defineNS_PER_MSINT64CONST(1000000)

#defineNS_PER_USINT64CONST(1000)

#if defined(__darwin__)|| defined(_MSC_VER)

#defineSUBMS_MINIMAL_STEP_BITS 10

#defineSUBMS_MINIMAL_STEP_BITS 12

#defineSUBMS_BITS12

#defineSUBMS_MINIMAL_STEP_NS ((NS_PER_MS / (1 << SUBMS_MINIMAL_STEP_BITS)) + 1)

{

staticintuuid_fast_cmp(Datumx,Datumy,SortSupportssup);

staticbooluuid_abbrev_abort(intmemtupcount,SortSupportssup);

staticDatumuuid_abbrev_convert(Datumoriginal,SortSupportssup);

staticinlinevoiduuid_set_version(pg_uuid_t*uuid,unsignedcharversion);

staticinlineint64get_real_time_ns_ascending();

uuid_in(PG_FUNCTION_ARGS)

returnhash_any_extended(key->data,UUID_LEN,PG_GETARG_INT64(1));

}

uuid_set_version(pg_uuid_t*uuid,unsignedcharversion)

{

uuid->data[6]= (uuid->data[6]&0x0f) | (version <<4);

uuid->data[8]= (uuid->data[8]&0x3f) |0x80;

}

gen_random_uuid(PG_FUNCTION_ARGS)

{

errmsg("could not generate random values")));

uuid->data[6]= (uuid->data[6]&0x0f) |0x40;/* time_hi_and_version */

uuid->data[8]= (uuid->data[8]&0x3f) |0x80;/* clock_seq_hi_and_reserved */

uuid_set_version(uuid,4);

PG_RETURN_UUID_P(uuid);

}

#defineUUIDV1_EPOCH_JDATE 2299161/* == date2j(1582,10,15) */

get_real_time_ns_ascending()

{

staticint64previous_ns=0;

int64ns;

structtimevaltmp;

gettimeofday(&tmp,NULL);

ns=tmp.tv_sec*NS_PER_S+tmp.tv_usec*NS_PER_US;

structtimespectmp;

clock_gettime(CLOCK_REALTIME,&tmp);

ns=tmp.tv_sec*NS_PER_S+tmp.tv_nsec;

if (previous_ns+SUBMS_MINIMAL_STEP_NS >=ns)

ns=previous_ns+SUBMS_MINIMAL_STEP_NS;

previous_ns=ns;

returnns;

}

generate_uuidv7(int64ns)

{

pg_uuid_t*uuid=palloc(UUID_LEN);

int64unix_ts_ms;

int32increased_clock_precision;

unix_ts_ms=ns /NS_PER_MS;

uuid->data[0]= (unsignedchar) (unix_ts_ms >>40);

uuid->data[1]= (unsignedchar) (unix_ts_ms >>32);

uuid->data[2]= (unsignedchar) (unix_ts_ms >>24);

uuid->data[3]= (unsignedchar) (unix_ts_ms >>16);

uuid->data[4]= (unsignedchar) (unix_ts_ms >>8);

uuid->data[5]= (unsignedchar)unix_ts_ms;

increased_clock_precision= ((ns %NS_PER_MS)* (1 <<SUBMS_BITS)) /NS_PER_MS;

uuid->data[6]= (unsignedchar) (increased_clock_precision >>8);

uuid->data[7]= (unsignedchar) (increased_clock_precision);

if (!pg_strong_random(&uuid->data[8],UUID_LEN-8))

ereport(ERROR,

(errcode(ERRCODE_INTERNAL_ERROR),

errmsg("could not generate random values")));

uuid->data[7]=uuid->data[7] ^ (uuid->data[8] >>6);

uuid_set_version(uuid,7);

returnuuid;

}

uuidv7(PG_FUNCTION_ARGS)

{

pg_uuid_t*uuid=generate_uuidv7(get_real_time_ns_ascending());

PG_RETURN_UUID_P(uuid);

}

uuidv7_interval(PG_FUNCTION_ARGS)

{

Interval*shift=PG_GETARG_INTERVAL_P(0);

TimestampTzts;

pg_uuid_t*uuid;

int64ns=get_real_time_ns_ascending();

ts= (TimestampTz) (ns /NS_PER_US)-

(POSTGRES_EPOCH_JDATE-UNIX_EPOCH_JDATE)*SECS_PER_DAY*USECS_PER_SEC;

ts=DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,

TimestampTzGetDatum(ts),

IntervalPGetDatum(shift)));

ns= (ts+ (POSTGRES_EPOCH_JDATE-UNIX_EPOCH_JDATE)*SECS_PER_DAY*USECS_PER_SEC)

*NS_PER_US+ns %NS_PER_US;

uuid=generate_uuidv7(ns);

PG_RETURN_UUID_P(uuid);

}

#defineGREGORIAN_EPOCH_JDATE INT64CONST(2299161)

uuid_extract_timestamp(PG_FUNCTION_ARGS)

uint64tms;

TimestampTzts;

if ((uuid->data[8]&0xc0)!=0x80)

PG_RETURN_NULL();

ts= (TimestampTz) (tms /10)-

((uint64)POSTGRES_EPOCH_JDATE-UUIDV1_EPOCH_JDATE)*SECS_PER_DAY*USECS_PER_SEC;

((uint64)POSTGRES_EPOCH_JDATE-GREGORIAN_EPOCH_JDATE)*SECS_PER_DAY*USECS_PER_SEC;

PG_RETURN_TIMESTAMPTZ(ts);

}

if (version==7)

{

tms= (uuid->data[5])

+ (((uint64)uuid->data[4]) <<8)

+ (((uint64)uuid->data[3]) <<16)

+ (((uint64)uuid->data[2]) <<24)

+ (((uint64)uuid->data[1]) <<32)

+ (((uint64)uuid->data[0]) <<40);

ts= (TimestampTz) (tms*NS_PER_US)-

(POSTGRES_EPOCH_JDATE-UNIX_EPOCH_JDATE)*SECS_PER_DAY*USECS_PER_SEC;

PG_RETURN_TIMESTAMPTZ(ts);

}

uuid_extract_version(PG_FUNCTION_ARGS)

{

pg_uuid_t*uuid=PG_GETARG_UUID_P(0);

uint16version;

if ((uuid->data[8]&0xc0)!=0x80)

PG_RETURN_NULL();