uuid-ossp: Unique Identifiers

The uuid-ossp extension can be used to generate a UUID.

Overview#

A UUID is a "Universally Unique Identifer" and it is, for practical purposes, unique. This makes them particularly well suited as Primary Keys. It is occasionally referred to as a GUID, which stands for "Globally Unique Identifer".

Usage#

Enabling#

Go to the Database page.
Click on "Extensions" in the sidebar.
Search for "uuid-ossp".
Click the toggle.

-- Example: enable the "uuid-ossp" extension 
create extension "uuid-ossp" with schema extensions;

-- Example: disable the "uuid-ossp" extension 
drop extension if exists "uuid-ossp";

Even though the SQL code is create extension, this is the equivalent of "enabling the extension". To disable an extension you can call drop extension.

It's good practice to create the extension within a separate schema (like extensions) to keep your database clean.

The `uuid` type#

Once the extension is enabled, you now have access to a uuid type.

`uuid_generate_v1()`#

Creates a UUID value based on the combination of computer’s MAC address, current timestamp, and a random value.

`uuid_generate_v4()`#

Creates UUID values based solely on random numbers.

Examples#

Within a query#

select uuid_generate_v4();

As a Primary Key#

Automatically create a unique, random ID in a table:

create table contacts (
  id uuid default uuid_generate_v4(),
  first_name text,
  last_name text,

  primary key (id)
);

Resources#

The Basics Of PostgreSQL UUID Data Type.