Need a UUID generator for Postgres? Here are two ways to set one up.

#database #tools #postgres

Postgres performs better than some other databases because it supports concurrent write operations without the need of read/write locks. Because it is completely ACID-compliant and provides transaction isolation and snapshots, many applications are using Postgres these days. Unfortunately, while PostgreSQL is great for storing and comparing UUID data, it lacks capabilities for creating UUID values in its core. Instead, it relies on third-party modules to create UUIDs using specified techniques. In this article, you'll learn about the PostgreSQL UUID data type and how to generate UUID values with examples utilizing various functions and modules.

What is a UUID?

UUID stands for Universal Unique Identifier, defined by RFC 4122 and other related standards. A UUID is a series of lower-case hexadecimal digits separated by hyphens. UUIDs are a combination of 36-character sequences of numbers, letters, and dashes that are intended to be globally unique.

Because of this fantastic characteristic, UUIDs are frequently used in distributed systems, since it ensures more uniqueness than the SERIAL data type, which creates only unique entries inside a single database. Separate computers can produce UUIDs at the same time without communicating, and the UUIDs will be guaranteed to be unique. Independent systems using UUIDs can be securely combined at any moment without fear of colliding. The uuid column data type in Postgres supports globally unique identifiers (UUIDs). You may need to produce a UUID if your table has a UUID column. Because no one technique is perfectly suited for every application, PostgreSQL has storage and comparison functions for UUIDs, but no function for generating UUIDs in the core database.

Why doesn't Postgres generate UUIDs itself?

PostgreSQL allows you to store and compare UUID values, but it doesn't have any built-in methods for creating them. This is why this post was developed - to show you several ways to generate UUIDS in Postgres.

How to generate UUIDS in Postgres

Method 1: Using the uuid-ossp module

As mentioned above, PostgreSQL allows you to store and compare UUID values, but it doesn't have any built-in methods for creating them. Instead, it relies on third-party modules that create UUIDs using specified techniques. The uuid-ossp module, for example, has various useful methods that implement common UUID generation techniques. To install the uuid-ossp module, you use the CREATE EXTENSION statement as follows:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

The IF NOT EXISTS clause allows you to avoid re-installing the module. To generate the UUID values based on the combination of the computer’s MAC address, current timestamp, and a random value, you use the uuid_generate_v1() function: SELECT uuid_generate_v1();

In this example, the function generated the following UUID value:

uuid_generate_v1
--------------------------------------
0e37df36-f698-11e6-8dd4-cb9ced3df976
(1 row)

If you want to generate a UUID value solely based on random numbers, you can use the uuid_generate_v4() function. For example:

SELECT uuid_generate_v4();

uuid_generate_v4
-------------------------------------- 
a81bc81b-dead-4e5d-abff-90865d1e13b1
(1 row)

For more information on the functions for UUID generation, check out the uuid-ossp module documentation.

Method 2: Using the gen_random_uuid() module

Additional provided modules are included in the PostgreSQL distribution, although they are not installed by default. Modules can be installed by any user account with the CREATE privilege. In the pgcrypto module, there is an uuid generating method called gen_random_uuid() that creates an uuid of using the Version 4 algorithm that is totally comprised of random hexadecimal integers.
The following SQL commands are used to find all available extensions. They also will display a list of those that are currently installed.

SELECT * FROM pg_avilable_extensions ORDER BY name; 
-- list available extensions

SELECT * FROM pg_extension; 
-- list installed extensions

To install pgcrypto extension, use the following command.

CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- creates extension

Once the pgcrypto extension is installed, the following returns a random UUID:

SELECT gen_random_uuid(); -- generate a random uuid of version 4
842d3fae-7788-4ecb-b441-7c7e8130b8bf

The difference between gen_random_uuid() and uuid_generate_v4()

Both gen_random_uuid() and uuid_generate_v4() generate UUIDs. The main difference between them is that gen_random_uuid() is provided by pgcrypto module while uuid_generate_v4() is provided by uuid-ossp extension. Each of these modules has limitations that should be considered in the context of your application.

The Postgres documentation on uuid-ossp suggests using gen_random_uuid(). If you only need randomly-generated (version 4) UUIDs. Furthermore, the uuid-ossp extension provides other types of UUID (such as mac-addresses based).
Another factor to consider is the different methods these tools use to generate their random values: uuid_generate_v4() uses arc4random to determine the random part, while gen_random_uuid() uses fortuna instead.

Should I use UUID as my primary key?

This is contingent on a number of things.

Since UUID is unique, you may use it as the main key in your database. Keep in mind, though, that UUID takes up a little more space than SEQUENCE. They're also slow to generate. However, they are unquestionably one-of-a-kind, ensuring that you will receive accurate data.
UUID numbers are unique across tables, databases, and even servers, allowing you to combine entries from several databases or spread databases over many servers. Because UUID values don't reveal information about your data, they're safer to use in URLs. This benefit could make the costs of using UUID worthwhile.

Conclusion

In PostgreSQL, there are several alternatives for ID fields, each of which has a suitable use case for whether to use or not use it. We looked at utilizing gen_random_uuid() and uuid-generate_v4() in this post. Choose the option that's best for your application, and remember to have fun!