DEFAULT when defining a column to invoke one of the OSSP uuid functions. The Postgres server will automatically invoke the function every time a row is inserted.
CREATE TABLE tbl ( pkey UUID NOT NULL DEFAULT uuid_generate_v1() , CONSTRAINT pkey_tbl PRIMARY KEY ( pkey ) )
Plugin Required To Generate UUID
While Postgres out-of-the-box supports storing UUID (Universally Unique Identifier) values in their native 128-bit form, generating UUID values requires a plug-in. In Postgres, a plug-in is known as an
The extension we want is an open-source library built in C for working with UUIDs, OSSP uuid. A build of this library for Postgres is often bundled with an installation of Postgres such as the graphical installers provided by Enterprise DB or included by cloud providers such as Amazon RDS for PostgreSQL.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Generating Various Kinds Of UUID
See the extension’s doc to see a list of multiple commands offered for generating various kinds of UUID values. To get the original version of UUID built from the computer’s MAC address plus current date-time plus a small random value, call
Later variations on this theme were developed for alternate kinds of UUIDs. Some people may not want to record the server’s actual MAC address, for example, for security or privacy concerns. The Postgres extension generates five kinds of UUIDs, plus the “nil” UUID
UUID As Default Value
That method call can be made automatically to generate a default value for any newly inserted row. When defining the column, specify:
See that command used in the following example table definition.
CREATE TABLE public.pet_ ( species_ text NOT NULL, name_ text NOT NULL, date_of_birth_ text NOT NULL, uuid_ uuid NOT NULL DEFAULT uuid_generate_v1(), -- <==== CONSTRAINT pet_pkey_ PRIMARY KEY (uuid_) ) WITH ( OIDS=FALSE ); ALTER TABLE public.pet_ OWNER TO postgres;
Contains MAC address of current computer + current moment. Commonly used, but avoid if you are sensitive about disclosing the MAC of your database server or the time when this value was generated. Defined by specification as a Version 1 UUID.
Like Version 1, but with a random multicast MAC address instead of the real MAC address. Apparently a way to use Version 1 but substituting another MAC rather than the actual MAC of your database server if you are sensitive about disclosing that fact.
What is a ‘random multicast MAC’? I do not know exactly. After reading section 4.1.6 of RFC 4122, I suspect this is a random number used in place of the MAC but with bits set to indicate a multicast MAC address rather than the usual unicast so as to distinguish this variation of Version 1 from a usual real-MAC Version 1 UUID.
uuid_generate_v3( namespace uuid, name text )
Contains an MD5 hash of text you provide. Defined by specification as a Version 3 UUID, namespace-based UUID.
Based on randomly-generated data for 121-122 of the 128 bits. Six or seven bits used to indicate Version & Variant. This kind of UUID is practical only if implemented with a cryptographically-strong random generator. Defined by specification as a Version 4 UUID.
uuid_generate_v5( namespace uuid, name text )
Same as Version 3 but using SHA1 hashing. Defined by specification as Version 5 UUID.
A special case, all bits set to zero
00000000-0000-0000-0000-000000000000. Used as a flag for an unknown UUID value. Known as a nil UUID.
To compare types, see Question, Which UUID version to use?
If you are interested only in Version 4 (randomly generated), and are already using pgcrypto, see the Answer by bpieck.
If you are curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.
Just a small addition to Basil’s very detailed answer.
First, enable pgcrypto in your Postgres.
CREATE EXTENSION "pgcrypto";
Just set DEFAULT of a column to
If using Postgres 13 or newer you do not need any modifications or extra plugins and can just use
just tested on default AWS Aurora Serverless v2 with Postgres v14
p.s. you can see my default id is actually with prefix
wor- while it's workspace id. I add prefixes to all tables so when i get ID from anywhere - logs, support etc. i can tell exactly what to search for.