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
Enable the extension
- Dashboard
- SQL
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "uuid-ossp" and enable the extension.
Note:
Currently uuid-ossp extension is enabled by default and cannot be disabled.
-- 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, call drop extension.
It's good practice to create the extension within a separate schema (like extensions) to keep your database clean.
Note:
Currently uuid-ossp extension is enabled by default and cannot be disabled.
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)
);