PostgreSQL Extensions: Intro to uuid-ossp

Written by Team Timescale

The uuid-ossp extension is a powerful tool for developers working with PostgreSQL. This extension generates universally unique identifiers (UUIDs) in accordance with the Open Software Foundation's (OSF) Distributed Computing Environment (DCE) standard.

UUIDs are 128-bit values that are globally unique. No matter where you generate a UUID, it will always be different from any UUID generated anywhere else at any time.

Installing the uuid-ossp Extension

To install the uuid-ossp extension, you need access to your PostgreSQL server and the necessary privileges to execute the CREATE EXTENSION command. Follow the steps below:

1. Log into your PostgreSQL server.

2. Load it into your shared_preloaded_libraries in postgresql.conf, as it will require additional shared memory. If you’re using Timescale, you can simply skip this step, as the extension is already preloaded.

3. Connect to the database where you want to install the extension.

4. Run the following SQL command:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

This command will install the uuid-ossp extension if it is not already installed. On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.

Using the uuid-ossp Extension

Once the uuid-ossp extension is installed, you can use it to generate UUIDs. The extension provides several functions for generating UUIDs, including:

- uuid_generate_v1(): This function generates a UUID using a combination of the current time and the MAC address of the computer executing the function.

- uuid_generate_v4(): This function generates a random UUID.

Here is an example of how to use these functions:

INSERT INTO my_table (id, name) VALUES (uuid_generate_v4(), 'My Name');

In this example, a new UUID is generated for each row inserted into the table. Note that PostgreSQL supports a UUID data type for your table.

uuid-ossp Functions

The uuid-ossp module in PostgreSQL provides a variety of functions to generate universally unique identifiers as well as constants that return specific UUID values. Here's an overview of some common functions and constants from the uuid-ossp module and their usage:

Functions for generating UUIDs

Function	Description
`uuid_generate_v1()`	Generates a UUID based on the host's MAC address and current time
`uuid_generate_v4()`	Generates a random UUID
`uuid_generate_v3(uuid, name)`	Generates a UUID using MD5 hashing of a namespace UUID and name
`uuid_generate_v5(uuid, name)`	Generates a UUID using SHA-1 hashing of a namespace UUID and name

Constants returning UUID values

Constant	Description
`uuid_nil()`	Returns the nil UUID: '00000000-0000-0000-0000-000000000000'
`uuid_ns_dns()`	Returns the UUID for the DNS namespace
`uuid_ns_url()`	Returns the UUID for the URL namespace
`uuid_ns_oid()`	Returns the UUID for the OID namespace
`uuid_ns_x500()`	Returns the UUID for the X.500 namespace

Example usage

To enable and use uuid-ossp functions and constants in PostgreSQL, first activate the extension: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Here’s how you can use the uuid_generate_v4() function to assign a unique identifier to a new record: INSERT INTO products (product_id, product_name, product_description) VALUES (uuid_generate_v4(), 'Widget', 'A useful widget');

To use a UUID constant in a query, such as assigning a specific namespace UUID to a variable or column: SELECT uuid_ns_dns() AS dns_namespace_uuid;

This example retrieves the UUID associated with the DNS namespace, which can be useful in applications requiring standardized UUID namespaces. The uuid-ossp functions and constants offer a robust toolset for creating and managing UUIDs in PostgreSQL, enhancing data uniqueness and integrity across distributed databases.

Time-Series Use Cases for the uuid-ossp Extension

The uuid-ossp extension is particularly useful in time-series data scenarios. For instance, in IoT applications where data is collected from multiple devices simultaneously, each data point can be assigned a unique UUID. This ensures that each data point can be uniquely identified, regardless of when or where it was collected.

Using uuid-ossp extension with Timescale and time-series data

If you're using Timescale, a high-performance time-series PostgreSQL++ database, the uuid-ossp extension can be particularly useful. You can use the uuid-ossp extension to generate a unique identifier for each time-series data point you insert into your Timescale database.

Here's an example:

INSERT INTO conditions (id, time, location, temperature, humidity) VALUES (uuid_generate_v4(), NOW(), 'office', 70.0, 50.0);

In this example, a new UUID is generated for each row inserted into the conditions table. This ensures that each data point can be uniquely identified, even if the time and location are the same for multiple data points.

Did you know Timescale can dramatically improve the performance for many UUID use cases? Check out our getting started guide!