Update docs for UUID data type
The attached patch file makes some modest changes to the docs for the UUID
data type (Section 8.12. UUID Type). The main goal is to inform the reader
that there are multiple versions of UUID generation algorithms (presently
8); however, once generated, PostgreSQL treats all UUIDs uniformly.
Regards,
Andy Alsup
Attachments:
0001-UUID-datatype-docs.patchapplication/octet-stream; name=0001-UUID-datatype-docs.patchDownload
From c91c28b207db88e0fda2429b27f447773f1e24b7 Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Fri, 21 Feb 2025 23:19:52 -0500
Subject: [PATCH] UUID datatype docs discuss different versions
---
doc/src/sgml/datatype.sgml | 21 +++++++++++++++------
1 file changed, 15 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 87679dc4a11..76a0bcc1bae 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4399,12 +4399,21 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
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
- identifier is a 128-bit quantity that is generated by an algorithm chosen
- to make it very unlikely that the same identifier will be generated by
- anyone else in the known universe using the same algorithm. Therefore,
- for distributed systems, these identifiers provide a better uniqueness
- guarantee than sequence generators, which
- are only unique within a single database.
+ identifier is a 128-bit quantity generated by an algorithm chosen to make it
+ extremely unlikely that the same identifier will be generated by any other system.
+ Therefore, for distributed systems, these identifiers offer better uniqueness
+ guarantees than sequence generators, which only guarantee uniqueness within a
+ single database.
+ </para>
+
+ <para>
+ The UUID RFC defines 8 discrete UUID versions, which differ only in how the
+ UUID is generated -- each version provides distinct benefits and drawbacks.
+ PostgreSQL provides native support for generating UUIDs using the UUIDv4 and
+ UUIDv7 algorithms. Alternatively, UUID values can be generated outside of the
+ PostgreSQL database. In any case, PostgreSQL supports the <type>uuid</type>
+ datatype uniformly, regardless of the UUID version or whether it was generated
+ internally or externally.
</para>
<para>
--
2.39.5 (Apple Git-154)
Please find the attached patch files that supersede the previous email.
Patch 0001 contains some modest modifications to the UUID data type docs
(Section 8.12. UUID Type). The main goal is to inform the reader that there
are multiple versions of UUID generation algorithms (presently 8); however,
once generated, PostgreSQL treats all UUIDs uniformly.
Patch 0002 contains modifications to the UUID functions docs (Section 9.14.
UUID Functions). The main goal is to format the UUID functions in table
form, similar to other function docs, such as Section 9.4. String Functions
and Operators. This provides the user a more consistent format, in line
with more established sections of the PostgreSQL documentation.
Thank you for your time and consideration.
Regards,
Andy Alsup
On Fri, Feb 21, 2025 at 11:42 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
Show quoted text
The attached patch file makes some modest changes to the docs for the UUID
data type (Section 8.12. UUID Type). The main goal is to inform the reader
that there are multiple versions of UUID generation algorithms (presently
8); however, once generated, PostgreSQL treats all UUIDs uniformly.Regards,
Andy Alsup
Attachments:
0001-docs-for-UUID-datatype-mention-UUID-versions.patchapplication/octet-stream; name=0001-docs-for-UUID-datatype-mention-UUID-versions.patchDownload
From 2dafa7c0f9840c1bee7774802c38a22abc4dc89a Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Fri, 21 Feb 2025 23:19:52 -0500
Subject: [PATCH 1/2] docs for UUID datatype mention UUID versions
---
doc/src/sgml/datatype.sgml | 21 +++++++++++++++------
1 file changed, 15 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 87679dc4a11..9841b125e06 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4399,12 +4399,21 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
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
- identifier is a 128-bit quantity that is generated by an algorithm chosen
- to make it very unlikely that the same identifier will be generated by
- anyone else in the known universe using the same algorithm. Therefore,
- for distributed systems, these identifiers provide a better uniqueness
- guarantee than sequence generators, which
- are only unique within a single database.
+ identifier is a 128-bit quantity generated by an algorithm chosen to make it
+ extremely unlikely that the same identifier will be generated by any other system.
+ Therefore, for distributed systems, these identifiers offer better uniqueness
+ guarantees than sequence generators, which only guarantee uniqueness within a
+ single database.
+ </para>
+
+ <para>
+ The UUID RFC defines 8 discrete UUID versions. Each version has specific requirements
+ for generating new UUID values, and each version provides distinct benefits and drawbacks.
+ PostgreSQL provides native support for generating UUIDs using the UUIDv4 and
+ UUIDv7 algorithms. Alternatively, UUID values can be generated outside of the
+ PostgreSQL database using any algorithm. In any case, PostgreSQL supports the
+ <type>uuid</type> datatype uniformly, regardless of the UUID version or whether it
+ was generated internally or externally.
</para>
<para>
--
2.39.5 (Apple Git-154)
0002-docs-for-UUID-funcs-formatted-in-table.patchapplication/octet-stream; name=0002-docs-for-UUID-funcs-formatted-in-table.patchDownload
From a66e6f4faec0f349313c174fe5b4f8a16107c70c Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Sun, 23 Feb 2025 21:58:54 -0500
Subject: [PATCH 2/2] docs for UUID funcs formatted in table
---
doc/src/sgml/func.sgml | 177 ++++++++++++++++++++++++++++++++---------
1 file changed, 141 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9f60a476eb3..1229f87cdf4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14327,55 +14327,160 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
<primary>uuid_extract_version</primary>
</indexterm>
- <para>
- <productname>PostgreSQL</productname> includes several 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 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>
+ <table id="func_uuid_gen_table">
+ <title><acronym>UUID</acronym> Generation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>gen_random_uuid</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>gen_random_uuid()</literal>
+ <returnvalue>5b30857f-0bfa-48b5-ac0b-5c64e28078d1</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv4</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>uuidv4()</literal>
+ <returnvalue>b42410ee-132f-42ee-9e4f-09a6485c95b8</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv7</type>
+ ( <optional> <parameter>shift</parameter> <type>interval</type> </optional> )
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp
+ with millisecond precision + sub-millisecond timestamp + random. The optional parameter
+ <parameter>shift</parameter> will shift the computed timestamp by the given <type>interval</type>.
+ </para>
+ <para>
+ <literal>uuidv7()</literal>
+ <returnvalue>019535d9-3df7-79fb-b466-fa907fa17f9e</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
<para>
The <xref linkend="uuid-ossp"/> module provides additional functions that
implement other standard algorithms for generating UUIDs.
</para>
- <para>
- There are also functions to extract data from UUIDs:
-<synopsis>
-<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 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>
+ <table id="func_uuid_extract_table">
+ <title><acronym>UUID</acronym> Extraction Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
- <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/rfc9562">RFC 9562</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.
- </para>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_timestamp</type>
+ ( <type>uuid</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Extracts a <type>timestamp with time zone</type> from 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>
+ <literal>uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>2025-02-23 21:46:24.503-05</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_version</type>
+ ( <type>uuid</type> )
+ <returnvalue>smallint</returnvalue>
+ </para>
+ <para>
+ Extracts the version from a UUID of the variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</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.
+ </para>
+ <para>
+ <literal>uuid_extract_version('41db1265-8bc1-4ab3-992f-885799a4af1d'::UUID)</literal>
+ <returnvalue>4</returnvalue>
+ </para>
+ <para>
+ <literal>uuid_extract_version('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
+ <para>
+ See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in
+ <productname>PostgreSQL</productname>.
+ </para>
</sect1>
<sect1 id="functions-xml">
--
2.39.5 (Apple Git-154)
On Sun, 2025-02-23 at 22:23 -0500, Andy Alsup wrote:
Please find the attached patch files that supersede the previous email.
Patch 0001 contains some modest modifications to the UUID data type docs
(Section 8.12. UUID Type). The main goal is to inform the reader that there
are multiple versions of UUID generation algorithms (presently 8); however,
once generated, PostgreSQL treats all UUIDs uniformly.Patch 0002 contains modifications to the UUID functions docs (Section 9.14.
UUID Functions). The main goal is to format the UUID functions in table form,
similar to other function docs, such as Section 9.4. String Functions and
Operators. This provides the user a more consistent format, in line with
more established sections of the PostgreSQL documentation.Thank you for your time and consideration.
I had a look at the patches.
About the first patch:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 87679dc4a11..9841b125e06 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4399,12 +4399,21 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' ); 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 - identifier is a 128-bit quantity that is generated by an algorithm chosen - to make it very unlikely that the same identifier will be generated by - anyone else in the known universe using the same algorithm. Therefore, - for distributed systems, these identifiers provide a better uniqueness - guarantee than sequence generators, which - are only unique within a single database. + identifier is a 128-bit quantity generated by an algorithm chosen to make it + extremely unlikely that the same identifier will be generated by any other system. + Therefore, for distributed systems, these identifiers offer better uniqueness + guarantees than sequence generators, which only guarantee uniqueness within a + single database. + </para> + + <para> + The UUID RFC defines 8 discrete UUID versions. Each version has specific requirements + for generating new UUID values, and each version provides distinct benefits and drawbacks. + PostgreSQL provides native support for generating UUIDs using the UUIDv4 and + UUIDv7 algorithms. Alternatively, UUID values can be generated outside of the + PostgreSQL database using any algorithm. In any case, PostgreSQL supports the + <type>uuid</type> datatype uniformly, regardless of the UUID version or whether it + was generated internally or externally.
"PostgreSQL" should wear a <productname> tag.
Your change to the first paragraph is just the removal of "that is" and
rearranging the line breaks. I don't think that the wording becomes any
clearer through that change, and it makes reading the patch more difficult.
It is a good idea to change as little as possible in the existing text
(particularly in the line breaks), so that reviewing becomes easier.
About the new paragraph: it should be "different", not "discrete".
I am not certain if the part after "alternatively" adds any relevant
information. Also, I am not certain what you mean with "uniformly".
Perhaps that sentence could be
The PostgreSQL data type <type>uuid</type> supports all kinds of UUIDs,
regardless of their version.
We don't mention that "integer" can be used to store integers generated
inside and outside PostgreSQL, so I don't think we need to mention that
here.
About the second patch:
A table is a good thing. We typically have an introductory paragraph
before such tables that contains a hyperlink to the table, something like
<xref ...> shows the <productname>PostgreSQL</productname> functions
that can be used to generate UUIDs:
Yours,
Laurenz Albe
--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.
Please find the attached patch, which only addresses the UUID functions (in
table format). I appreciate the comments related to the UUID datatype. If
you feel like the additional content didn't add clarity, I certainly won't
argue.
Best regards,
Andy Alsup
On Mon, Feb 24, 2025 at 2:02 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Sun, 2025-02-23 at 22:23 -0500, Andy Alsup wrote:
Please find the attached patch files that supersede the previous email.
Patch 0001 contains some modest modifications to the UUID data type docs
(Section 8.12. UUID Type). The main goal is to inform the reader thatthere
are multiple versions of UUID generation algorithms (presently 8);
however,
once generated, PostgreSQL treats all UUIDs uniformly.
Patch 0002 contains modifications to the UUID functions docs
(Section 9.14.
UUID Functions). The main goal is to format the UUID functions in table
form,
similar to other function docs, such as Section 9.4. String Functions and
Operators. This provides the user a more consistent format, in line with
more established sections of the PostgreSQL documentation.Thank you for your time and consideration.
I had a look at the patches.
About the first patch:
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 87679dc4a11..9841b125e06 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4399,12 +4399,21 @@ SELECT to_tsvector( 'postgraduate' ),to_tsquery( 'postgres:*' );
ISO/IEC 9834-8:2005, and related standards.
(Some systems refer to this data type as a globally uniqueidentifier, or
GUID,<indexterm><primary>GUID</primary></indexterm> instead.) This
- identifier is a 128-bit quantity that is generated by an algorithmchosen
- to make it very unlikely that the same identifier will be generated
by
- anyone else in the known universe using the same algorithm.
Therefore,
- for distributed systems, these identifiers provide a better
uniqueness
- guarantee than sequence generators, which - are only unique within a single database. + identifier is a 128-bit quantity generated by an algorithm chosento make it
+ extremely unlikely that the same identifier will be generated by
any other system.
+ Therefore, for distributed systems, these identifiers offer better
uniqueness
+ guarantees than sequence generators, which only guarantee
uniqueness within a
+ single database. + </para> + + <para> + The UUID RFC defines 8 discrete UUID versions. Each version hasspecific requirements
+ for generating new UUID values, and each version provides distinct
benefits and drawbacks.
+ PostgreSQL provides native support for generating UUIDs using the
UUIDv4 and
+ UUIDv7 algorithms. Alternatively, UUID values can be generated
outside of the
+ PostgreSQL database using any algorithm. In any case, PostgreSQL
supports the
+ <type>uuid</type> datatype uniformly, regardless of the UUID
version or whether it
+ was generated internally or externally.
"PostgreSQL" should wear a <productname> tag.
Your change to the first paragraph is just the removal of "that is" and
rearranging the line breaks. I don't think that the wording becomes any
clearer through that change, and it makes reading the patch more difficult.
It is a good idea to change as little as possible in the existing text
(particularly in the line breaks), so that reviewing becomes easier.About the new paragraph: it should be "different", not "discrete".
I am not certain if the part after "alternatively" adds any relevant
information. Also, I am not certain what you mean with "uniformly".
Perhaps that sentence could beThe PostgreSQL data type <type>uuid</type> supports all kinds of UUIDs,
regardless of their version.We don't mention that "integer" can be used to store integers generated
inside and outside PostgreSQL, so I don't think we need to mention that
here.About the second patch:
A table is a good thing. We typically have an introductory paragraph
before such tables that contains a hyperlink to the table, something like<xref ...> shows the <productname>PostgreSQL</productname> functions
that can be used to generate UUIDs:Yours,
Laurenz Albe--
*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message
and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful,
and
you must not copy this message or attachment or disclose the contents to
any other person.
Attachments:
v2-0001-docs-for-UUID-funcs-formatted-in-table.patchapplication/octet-stream; name=v2-0001-docs-for-UUID-funcs-formatted-in-table.patchDownload
From 46691a0c60bfb161aae96f38ce11ae1060b70170 Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Mon, 24 Feb 2025 20:56:22 -0500
Subject: [PATCH v2] docs for UUID funcs formatted in table
---
doc/src/sgml/func.sgml | 189 +++++++++++++++++++++++++++++++++--------
1 file changed, 153 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 21a8ea42f5f..f560ebd11d4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14328,54 +14328,171 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes several 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 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.
+ <xref linkend="func_uuid_gen_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to generate UUIDs.
</para>
- <para>
- The <xref linkend="uuid-ossp"/> module provides additional functions that
- implement other standard algorithms for generating UUIDs.
- </para>
+ <table id="func_uuid_gen_table">
+ <title><acronym>UUID</acronym> Generation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
- <para>
- There are also functions to extract data from UUIDs:
-<synopsis>
-<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 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>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>gen_random_uuid</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>gen_random_uuid()</literal>
+ <returnvalue>5b30857f-0bfa-48b5-ac0b-5c64e28078d1</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv4</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>uuidv4()</literal>
+ <returnvalue>b42410ee-132f-42ee-9e4f-09a6485c95b8</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv7</type>
+ ( <optional> <parameter>shift</parameter> <type>interval</type> </optional> )
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp
+ with millisecond precision + sub-millisecond timestamp + random. The optional parameter
+ <parameter>shift</parameter> will shift the computed timestamp by the given <type>interval</type>.
+ </para>
+ <para>
+ <literal>uuidv7()</literal>
+ <returnvalue>019535d9-3df7-79fb-b466-fa907fa17f9e</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The <xref linkend="uuid-ossp"/> module provides additional functions that
+ implement other standard algorithms for generating UUIDs.
+ </para>
+ </note>
<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/rfc9562">RFC 9562</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.
+ <xref linkend="func_uuid_extract_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to extract information from UUIDs.
</para>
+ <table id="func_uuid_extract_table">
+ <title><acronym>UUID</acronym> Extraction Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_timestamp</type>
+ ( <type>uuid</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Extracts a <type>timestamp with time zone</type> from 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>
+ <literal>uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>2025-02-23 21:46:24.503-05</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_version</type>
+ ( <type>uuid</type> )
+ <returnvalue>smallint</returnvalue>
+ </para>
+ <para>
+ Extracts the version from a UUID of the variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</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.
+ </para>
+ <para>
+ <literal>uuid_extract_version('41db1265-8bc1-4ab3-992f-885799a4af1d'::UUID)</literal>
+ <returnvalue>4</returnvalue>
+ </para>
+ <para>
+ <literal>uuid_extract_version('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
+ <para>
+ See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in
+ <productname>PostgreSQL</productname>.
+ </para>
</sect1>
<sect1 id="functions-xml">
--
2.39.5 (Apple Git-154)
On Mon, 2025-02-24 at 21:04 -0500, Andy Alsup wrote:
Please find the attached patch, which only addresses the UUID functions
(in table format). I appreciate the comments related to the UUID datatype.
If you feel like the additional content didn't add clarity, I certainly won't argue.
Your patch looks good to me.
I didn't mean that adding more information about the "uuid" data type is
a bad thing. Perhaps that additional paragraph could be
RFC 9562 defines 8 different UUID versions. Each version has specific requirements
for generating new UUID values, and each version provides distinct benefits and drawbacks.
<productname>PostgreSQL</productname> provides native support for generating UUIDs
using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values can be generated
outside of the database using any algorithm. The data type <type>uuid</type> can be used
to store any UUID, regardless of the origin and the UUID version.
I would be happy if you added something like that again.
Yours,
Laurenz Albe
Thank you for the clarification, and the well-worded paragraph. Please find
the latest patch files attached.
Best regards,
Andy Alsup
On Tue, Feb 25, 2025 at 12:41 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Mon, 2025-02-24 at 21:04 -0500, Andy Alsup wrote:
Please find the attached patch, which only addresses the UUID functions
(in table format). I appreciate the comments related to the UUIDdatatype.
If you feel like the additional content didn't add clarity, I certainly
won't argue.
Your patch looks good to me.
I didn't mean that adding more information about the "uuid" data type is
a bad thing. Perhaps that additional paragraph could beRFC 9562 defines 8 different UUID versions. Each version has specific
requirements
for generating new UUID values, and each version provides distinct
benefits and drawbacks.
<productname>PostgreSQL</productname> provides native support for
generating UUIDs
using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values
can be generated
outside of the database using any algorithm. The data type
<type>uuid</type> can be used
to store any UUID, regardless of the origin and the UUID version.I would be happy if you added something like that again.
Yours,
Laurenz Albe
Attachments:
v3-0001-docs-for-UUID-funcs-formatted-in-table.patchapplication/octet-stream; name=v3-0001-docs-for-UUID-funcs-formatted-in-table.patchDownload
From 98b9447eb3081f3c1545b435900392739bb18732 Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Mon, 24 Feb 2025 20:56:22 -0500
Subject: [PATCH v3 1/2] docs for UUID funcs formatted in table
---
doc/src/sgml/func.sgml | 189 +++++++++++++++++++++++++++++++++--------
1 file changed, 153 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0e6c5349652..ca06d9b3270 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14328,54 +14328,171 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes several 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 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.
+ <xref linkend="func_uuid_gen_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to generate UUIDs.
</para>
- <para>
- The <xref linkend="uuid-ossp"/> module provides additional functions that
- implement other standard algorithms for generating UUIDs.
- </para>
+ <table id="func_uuid_gen_table">
+ <title><acronym>UUID</acronym> Generation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
- <para>
- There are also functions to extract data from UUIDs:
-<synopsis>
-<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 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>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>gen_random_uuid</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>gen_random_uuid()</literal>
+ <returnvalue>5b30857f-0bfa-48b5-ac0b-5c64e28078d1</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv4</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>uuidv4()</literal>
+ <returnvalue>b42410ee-132f-42ee-9e4f-09a6485c95b8</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv7</type>
+ ( <optional> <parameter>shift</parameter> <type>interval</type> </optional> )
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp
+ with millisecond precision + sub-millisecond timestamp + random. The optional parameter
+ <parameter>shift</parameter> will shift the computed timestamp by the given <type>interval</type>.
+ </para>
+ <para>
+ <literal>uuidv7()</literal>
+ <returnvalue>019535d9-3df7-79fb-b466-fa907fa17f9e</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The <xref linkend="uuid-ossp"/> module provides additional functions that
+ implement other standard algorithms for generating UUIDs.
+ </para>
+ </note>
<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/rfc9562">RFC 9562</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.
+ <xref linkend="func_uuid_extract_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to extract information from UUIDs.
</para>
+ <table id="func_uuid_extract_table">
+ <title><acronym>UUID</acronym> Extraction Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_timestamp</type>
+ ( <type>uuid</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Extracts a <type>timestamp with time zone</type> from 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>
+ <literal>uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>2025-02-23 21:46:24.503-05</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_version</type>
+ ( <type>uuid</type> )
+ <returnvalue>smallint</returnvalue>
+ </para>
+ <para>
+ Extracts the version from a UUID of the variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</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.
+ </para>
+ <para>
+ <literal>uuid_extract_version('41db1265-8bc1-4ab3-992f-885799a4af1d'::UUID)</literal>
+ <returnvalue>4</returnvalue>
+ </para>
+ <para>
+ <literal>uuid_extract_version('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
+ <para>
+ See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in
+ <productname>PostgreSQL</productname>.
+ </para>
</sect1>
<sect1 id="functions-xml">
--
2.39.5 (Apple Git-154)
v3-0002-docs-for-UUID-datatype-mention-UUID-versions.patchapplication/octet-stream; name=v3-0002-docs-for-UUID-datatype-mention-UUID-versions.patchDownload
From 132e78af46a1fd21068cf795f4427f57c19e356a Mon Sep 17 00:00:00 2001
From: Andrew Alsup <andrew.alsup@lightsecondengineering.com>
Date: Wed, 26 Feb 2025 22:00:14 -0500
Subject: [PATCH v3 2/2] docs for UUID datatype mention UUID versions
---
doc/src/sgml/datatype.sgml | 9 +++++++++
1 file changed, 9 insertions(+)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 87679dc4a11..09309ba0390 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4407,6 +4407,15 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
are only unique within a single database.
</para>
+ <para>
+ RFC 9562 defines 8 different UUID versions. Each version has specific requirements
+ for generating new UUID values, and each version provides distinct benefits and drawbacks.
+ <productname>PostgreSQL</productname> provides native support for generating UUIDs
+ using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values can be generated
+ outside of the database using any algorithm. The data type <type>uuid</type> can be used
+ to store any UUID, regardless of the origin and the UUID version.
+ </para>
+
<para>
A UUID is written as a sequence of lower-case hexadecimal digits,
in several groups separated by hyphens, specifically a group of 8
--
2.39.5 (Apple Git-154)
On Wed, 2025-02-26 at 22:11 -0500, Andy Alsup wrote:
Please find the latest patch files attached.
This is good to go. If you add it to the commitfest, I'm happy to
mark it "ready for committer".
Yours,
Laurenz Albe
I've submitted it for the up-coming commitfest. The link is:
https://commitfest.postgresql.org/patch/5604/
Thanks for all your help in reviewing these changes.
Best Regards,
Andy Alsup
On Thu, Feb 27, 2025 at 1:58 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Wed, 2025-02-26 at 22:11 -0500, Andy Alsup wrote:
Please find the latest patch files attached.
This is good to go. If you add it to the commitfest, I'm happy to
mark it "ready for committer".Yours,
Laurenz Albe
On Thu, Feb 27, 2025 at 1:26 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
I've submitted it for the up-coming commitfest. The link is: https://commitfest.postgresql.org/patch/5604/
Thanks for all your help in reviewing these changes.
Thank you for the patch!
Regarding the 0001 patch, I think we can put uuidv4() and
get_random_uuid() in the same row since they are effectively identical
functions. For example, we have precedent such as char_length() and
character_length().
The 0002 patch looks good to me.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Masahiko,
I have combined the gen_random_uuid() and uuidv4() into a single row, as
you suggested. Please find the v5 patch, which has been squashed into a
single commit.
Best regards,
Andy Alsup
On Thu, Feb 27, 2025 at 5:02 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:
Show quoted text
On Thu, Feb 27, 2025 at 1:26 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
I've submitted it for the up-coming commitfest. The link is:
https://commitfest.postgresql.org/patch/5604/
Thanks for all your help in reviewing these changes.
Thank you for the patch!
Regarding the 0001 patch, I think we can put uuidv4() and
get_random_uuid() in the same row since they are effectively identical
functions. For example, we have precedent such as char_length() and
character_length().The 0002 patch looks good to me.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
v5-0001-Docs-for-UUID-funcs-formatted-in-table-and-UUID-d.patchapplication/octet-stream; name=v5-0001-Docs-for-UUID-funcs-formatted-in-table-and-UUID-d.patchDownload
From 79de2a8f6efedae041dad87eb477a89d66c3632e Mon Sep 17 00:00:00 2001
From: Andy Alsup <bluesbreaker@gmail.com>
Date: Mon, 24 Feb 2025 20:56:22 -0500
Subject: [PATCH v5] Docs for UUID funcs formatted in table and UUID datatype
describes versions
combine gen_random_uuid() and uuidv4() into the same table row
---
doc/src/sgml/datatype.sgml | 9 ++
doc/src/sgml/func.sgml | 182 +++++++++++++++++++++++++++++--------
2 files changed, 155 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 87679dc4a11..09309ba0390 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4407,6 +4407,15 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
are only unique within a single database.
</para>
+ <para>
+ RFC 9562 defines 8 different UUID versions. Each version has specific requirements
+ for generating new UUID values, and each version provides distinct benefits and drawbacks.
+ <productname>PostgreSQL</productname> provides native support for generating UUIDs
+ using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values can be generated
+ outside of the database using any algorithm. The data type <type>uuid</type> can be used
+ to store any UUID, regardless of the origin and the UUID version.
+ </para>
+
<para>
A UUID is written as a sequence of lower-case hexadecimal digits,
in several groups separated by hyphens, specifically a group of 8
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0e6c5349652..4d70bd2a975 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14328,54 +14328,164 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes several 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 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.
+ <xref linkend="func_uuid_gen_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to generate UUIDs.
</para>
- <para>
- The <xref linkend="uuid-ossp"/> module provides additional functions that
- implement other standard algorithms for generating UUIDs.
- </para>
+ <table id="func_uuid_gen_table">
+ <title><acronym>UUID</acronym> Generation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
- <para>
- There are also functions to extract data from UUIDs:
-<synopsis>
-<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 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>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>gen_random_uuid</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para role="func_signature">
+ <type>uuidv4</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>gen_random_uuid()</literal>
+ <returnvalue>5b30857f-0bfa-48b5-ac0b-5c64e28078d1</returnvalue>
+ </para>
+ <para>
+ <literal>uuidv4()</literal>
+ <returnvalue>b42410ee-132f-42ee-9e4f-09a6485c95b8</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv7</type>
+ ( <optional> <parameter>shift</parameter> <type>interval</type> </optional> )
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp
+ with millisecond precision + sub-millisecond timestamp + random. The optional parameter
+ <parameter>shift</parameter> will shift the computed timestamp by the given <type>interval</type>.
+ </para>
+ <para>
+ <literal>uuidv7()</literal>
+ <returnvalue>019535d9-3df7-79fb-b466-fa907fa17f9e</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The <xref linkend="uuid-ossp"/> module provides additional functions that
+ implement other standard algorithms for generating UUIDs.
+ </para>
+ </note>
<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/rfc9562">RFC 9562</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.
+ <xref linkend="func_uuid_extract_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to extract information from UUIDs.
</para>
+ <table id="func_uuid_extract_table">
+ <title><acronym>UUID</acronym> Extraction Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_timestamp</type>
+ ( <type>uuid</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Extracts a <type>timestamp with time zone</type> from 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>
+ <literal>uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>2025-02-23 21:46:24.503-05</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_version</type>
+ ( <type>uuid</type> )
+ <returnvalue>smallint</returnvalue>
+ </para>
+ <para>
+ Extracts the version from a UUID of the variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</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.
+ </para>
+ <para>
+ <literal>uuid_extract_version('41db1265-8bc1-4ab3-992f-885799a4af1d'::UUID)</literal>
+ <returnvalue>4</returnvalue>
+ </para>
+ <para>
+ <literal>uuid_extract_version('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
+ <para>
+ See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in
+ <productname>PostgreSQL</productname>.
+ </para>
</sect1>
<sect1 id="functions-xml">
--
2.39.5 (Apple Git-154)
On Thu, Feb 27, 2025 at 5:50 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
Masahiko,
I have combined the gen_random_uuid() and uuidv4() into a single row, as you suggested. Please find the v5 patch, which has been squashed into a single commit.
Thank you for updating the patch!
I like that the patch adds the reference to the uuid data type. But I
think we might want to adjust terminology:
+ <para>
+ See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in
+ <productname>PostgreSQL</productname>.
+ </para>On 9.14. UUID Functions section, we use the word 'UUID' for data that
are generated based on algorithms defined by RFC9562 whereas we use
uuid (i.e., <type>uuid</type> in func.sgml) to a PostgreSQL data type.
IIUC you want to refer 'UUID datatype' in the above change to the
latter, PostgreSQL's uuid data type. Is that correct? If so, how about
the following change?
See <xref linkend="datatype-uuid"/> for details on the data type
<type>uuid</type> in <productname>PostgreSQL</productname>.
I've attached the updated patch that incorporates the above change,
and updated the commit message too.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
0001-doc-Convert-UUID-functions-list-to-table-format.patchapplication/octet-stream; name=0001-doc-Convert-UUID-functions-list-to-table-format.patchDownload
From 89e579fb1492f5a6164c5e34b76b6d3f62dffa3b Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 28 Feb 2025 10:42:21 -0800
Subject: [PATCH] doc: Convert UUID functions list to table format.
Convert the list of UUID functions into a table for better
readability. This commit also adds references to the UUID type section
and include descriptions of different UUID generation algorithm
versions.
Author: Andy Alsup <bluesbreaker@gmail.com>
Reviewed-by: Laurenz Albe <laurenz.albe@cybertec.at>
Discussion: https://postgr.es/m/CADOZ7s7OHag+r6w+BzKw2xgb3fVtAD-pU=_N9-9pSe5W1TB+xQ@mail.gmail.com
---
doc/src/sgml/datatype.sgml | 9 ++
doc/src/sgml/func.sgml | 182 +++++++++++++++++++++++++++++--------
2 files changed, 155 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 87679dc4a11..09309ba0390 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -4407,6 +4407,15 @@ SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
are only unique within a single database.
</para>
+ <para>
+ RFC 9562 defines 8 different UUID versions. Each version has specific requirements
+ for generating new UUID values, and each version provides distinct benefits and drawbacks.
+ <productname>PostgreSQL</productname> provides native support for generating UUIDs
+ using the UUIDv4 and UUIDv7 algorithms. Alternatively, UUID values can be generated
+ outside of the database using any algorithm. The data type <type>uuid</type> can be used
+ to store any UUID, regardless of the origin and the UUID version.
+ </para>
+
<para>
A UUID is written as a sequence of lower-case hexadecimal digits,
in several groups separated by hyphens, specifically a group of 8
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0e6c5349652..e0f5a346587 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14328,54 +14328,164 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
</indexterm>
<para>
- <productname>PostgreSQL</productname> includes several 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 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.
+ <xref linkend="func_uuid_gen_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to generate UUIDs.
</para>
- <para>
- The <xref linkend="uuid-ossp"/> module provides additional functions that
- implement other standard algorithms for generating UUIDs.
- </para>
+ <table id="func_uuid_gen_table">
+ <title><acronym>UUID</acronym> Generation Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
- <para>
- There are also functions to extract data from UUIDs:
-<synopsis>
-<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 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>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>gen_random_uuid</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para role="func_signature">
+ <type>uuidv4</type>
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 4 (random) UUID.
+ </para>
+ <para>
+ <literal>gen_random_uuid()</literal>
+ <returnvalue>5b30857f-0bfa-48b5-ac0b-5c64e28078d1</returnvalue>
+ </para>
+ <para>
+ <literal>uuidv4()</literal>
+ <returnvalue>b42410ee-132f-42ee-9e4f-09a6485c95b8</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuidv7</type>
+ ( <optional> <parameter>shift</parameter> <type>interval</type> </optional> )
+ <returnvalue>uuid</returnvalue>
+ </para>
+ <para>
+ Generate a version 7 (time-ordered) UUID. The timestamp is computed using UNIX timestamp
+ with millisecond precision + sub-millisecond timestamp + random. The optional parameter
+ <parameter>shift</parameter> will shift the computed timestamp by the given <type>interval</type>.
+ </para>
+ <para>
+ <literal>uuidv7()</literal>
+ <returnvalue>019535d9-3df7-79fb-b466-fa907fa17f9e</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <note>
+ <para>
+ The <xref linkend="uuid-ossp"/> module provides additional functions that
+ implement other standard algorithms for generating UUIDs.
+ </para>
+ </note>
<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/rfc9562">RFC 9562</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.
+ <xref linkend="func_uuid_extract_table"/> shows the <productname>PostgreSQL</productname>
+ functions that can be used to extract information from UUIDs.
</para>
+ <table id="func_uuid_extract_table">
+ <title><acronym>UUID</acronym> Extraction Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para>
+ </entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_timestamp</type>
+ ( <type>uuid</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Extracts a <type>timestamp with time zone</type> from 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>
+ <literal>uuid_extract_timestamp('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>2025-02-23 21:46:24.503-05</returnvalue>
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <type>uuid_extract_version</type>
+ ( <type>uuid</type> )
+ <returnvalue>smallint</returnvalue>
+ </para>
+ <para>
+ Extracts the version from a UUID of the variant described by
+ <ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</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.
+ </para>
+ <para>
+ <literal>uuid_extract_version('41db1265-8bc1-4ab3-992f-885799a4af1d'::UUID)</literal>
+ <returnvalue>4</returnvalue>
+ </para>
+ <para>
+ <literal>uuid_extract_version('019535d9-3df7-79fb-b466-fa907fa17f9e'::UUID)</literal>
+ <returnvalue>7</returnvalue>
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
+ <para>
+ See <xref linkend="datatype-uuid"/> for details on the data type
+ <type>uuid</type> in <productname>PostgreSQL</productname>.
+ </para>
</sect1>
<sect1 id="functions-xml">
--
2.43.5
Masahiko,
I like the change you've made.
Thanks,
Andy Alsup
On Fri, Feb 28, 2025 at 2:05 PM Masahiko Sawada <sawada.mshk@gmail.com>
wrote:
Show quoted text
On Thu, Feb 27, 2025 at 5:50 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
Masahiko,
I have combined the gen_random_uuid() and uuidv4() into a single row, as
you suggested. Please find the v5 patch, which has been squashed into a
single commit.Thank you for updating the patch!
I like that the patch adds the reference to the uuid data type. But I
think we might want to adjust terminology:+ <para> + See <xref linkend="datatype-uuid"/> for how details on the UUID datatype in + <productname>PostgreSQL</productname>. + </para>On 9.14. UUID Functions section, we use the word 'UUID' for data that
are generated based on algorithms defined by RFC9562 whereas we use
uuid (i.e., <type>uuid</type> in func.sgml) to a PostgreSQL data type.
IIUC you want to refer 'UUID datatype' in the above change to the
latter, PostgreSQL's uuid data type. Is that correct? If so, how about
the following change?See <xref linkend="datatype-uuid"/> for details on the data type
<type>uuid</type> in <productname>PostgreSQL</productname>.I've attached the updated patch that incorporates the above change,
and updated the commit message too.Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
On Fri, Feb 28, 2025 at 1:44 PM Andy Alsup <bluesbreaker@gmail.com> wrote:
Masahiko,
I like the change you've made.
Pushed.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com