{"id":773,"date":"2024-12-09T08:16:34","date_gmt":"2024-12-09T01:16:34","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=773"},"modified":"2025-01-02T14:28:18","modified_gmt":"2025-01-02T07:28:18","slug":"postgresql-uuid","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-uuid\/","title":{"rendered":"PostgreSQL UUID Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to store UUID<\/code> values using the PostgreSQL UUID<\/code> type.<\/p>\n\n\n\n

Getting Started with PostgreSQL UUID type #<\/a><\/h2>\n\n\n\n

UUID<\/code> stands for universal unique identifier and is a standard defined by the RFC 4122<\/a>.<\/p>\n\n\n\n

A UUID<\/code> is 128 bits long and does not require central registration. It guarantees uniqueness across systems and time. PostgreSQL uses the UUID<\/code> type for storing UUID<\/code> values.<\/p>\n\n\n\n

Here’s the syntax for defining a UUID<\/code> column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span> (\n\u00a0 \u00a0column_name<\/span> UUID<\/span>,\n   ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span> (\n\u00a0 \u00a0column_name<\/span> UUID<\/span> PRIMARY KEY<\/span>,\n   ...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that UUID<\/code> is unique and does not expose the internal sequence to the public.<\/p>\n\n\n\n
For example, if you have a customer with ID 10000, it may expose that your system has 10K customers.<\/p>\n\n\n\n
Your competitor may use this information to gain a competitive advantage when they know the number of customers you have.<\/p>\n\n\n\n
Using UUID as the primary key, both PostgreSQL and your application can generate it while maintaining uniqueness.<\/p>\n\n\n\n
For example, you can generate a key (UUID<\/code>) to insert a new customer and use it to create a customer profile.<\/p>\n\n\n
\/\/ Generate UUID<\/span> for<\/span> a customer\n\/\/ Use UUID<\/span> to<\/span> create<\/span> a customer profile<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you use the identity column, you have to insert a new customer first, get the generated ID by the PostgreSQL, and use that ID to create a customer profile.<\/p>\n\n\n\n
Function to generate UUID values #<\/a><\/h2>\n\n\n\n
PostgreSQL provides the gen_random_uuid()<\/code> that allows you to generate a UUID<\/code> value. For example, the following statement generates a UUID<\/code> value:<\/p>\n\n\n
SELECT<\/span> gen_random_uuid();<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
           gen_random_uuid\n--------------------------------------<\/span>\n 68<\/span>f375c8-1<\/span>b55-4558<\/span>-a176-72<\/span>a38a520149<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Each time you execute the function, it generates a new UUID<\/code> value.<\/p>\n\n\n\n
You can use the gen_random_uuid()<\/code> function to generate default values for the UUID<\/code> primary key column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n \u00a0\u00a0user_id UUID<\/span> PRIMARY KEY<\/span> DEFAULT<\/span> gen_random_uuid(),\n\u00a0 \u00a0...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
PostgreSQL UUID data type example #<\/a><\/h2>\n\n\n\n
First, create a table<\/a> called customers<\/code> that uses the UUID<\/code> type as the primary key column<\/a>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> customers (\n  customer_id UUID<\/span> PRIMARY KEY<\/span> DEFAULT<\/span> gen_random_uuid(),\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the customers<\/code> table, the customer_id<\/code> column is a primary key column. The type of the customer_id<\/code> column is UUID<\/code>.<\/p>\n\n\n\n
When you insert a row into the table, PostgreSQL automatically generates UUID<\/code> values for the customer_id<\/code> column by calling the gen_random_uuid()<\/code> function.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span>\n  customers (name<\/span>)\nVALUES<\/span>\n  ('ABC Corp.'<\/span>),\n  ('XYZ Inc.'<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
             customer_id              |   name<\/span>\n--------------------------------------+-----------<\/span>\n f1af387f-ac03-4784<\/span>-bbcb-b564a88cd682 | ABC Corp.\n aae1c410-06<\/span>ff-48<\/span>df-818<\/span>b-dbed92a1efbc | XYZ Inc.<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Second, insert data<\/a> into the customers<\/code> table:<\/p>\n\n\n
Typically, we use the UUID<\/code> as the type of a primary key<\/a> column of the table:<\/p>\n\n\n