53

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?

72

tl;dr

Call 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 extension.

To install an extension, call CREATE EXTENSION. To avoid re-installing, add IF NOT EXISTS. See my blog post for more details, or see this page in StackOverflow.

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 uuid_generate_v1().

SELECT uuid_generate_v1();

672124b6-9894-11e5-be38-001d42e813fe

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 00000000-0000-0000-0000-000000000000.

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:

DEFAULT uuid_generate_v1()

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;

UUID versions

The uuid-ossp plugin can generate various versions of UUID.

  • uuid_generate_v1()
    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.
  • uuid_generate_v1mc()
    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.
  • uuid_generate_v4()
    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.
  • uuid_nil()
    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 curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.

For more discussion, see my Answer to a similar Question and my blog post UUID values from JDBC to Postgres.

  • 1,000,000 hits in Google for how to create columns of type UUID. Zero hits for how to freaking query rows by that pk!! :-@ – Clint Eastwood Jul 9 '18 at 17:31
  • @ClintEastwood My Answer to a similar Question and my blog post UUID values from JDBC to Postgres might help you with that. If those prove to be insufficient, post a new Question. I’d be happy to take another stab at it. I understand your frustration! – Basil Bourque Jul 9 '18 at 19:30
  • @ClintEastwood: the syntax to compare a UUID column with a value follows the syntax for constants documented in the manual: postgresql.org/docs/current/static/… and type casts: postgresql.org/docs/current/static/… – a_horse_with_no_name Jul 9 '18 at 19:51
  • 1
    @BasilBourque: It's not really necessary to cast the result of getObject() you can also use UUID id = rs.getObject("uuid_", UUID.class); – a_horse_with_no_name Jul 9 '18 at 19:53
  • 1
    @Rokit To verify the strength of the random number generator, look to the underlying open-source implementation that is wrapped by this Postgres extension, the OSSP uuid library project as mentioned in my Answer. And remember, Version 4 UUIDs should be used only as a last resort, if for some reason you cannot choose the other types. Generally, your first choice should be the Version 1 kind, by calling either uuid_generate_v1 or uuid_generate_v1mc. – Basil Bourque Jan 19 at 1:11
3

pgcrypto extension

Just a small addition to Basil’s very detailed answer:

Since currently most are using pgcrypto, instead of uuid_generate_v1() you can use gen_random_uuid() for a Version 4 UUID value.

First, enable pgcrypto in your Postgres.

CREATE EXTENSION "pgcrypto";

Just set DEFAULT of a column to DEFAULT gen_random_uuid()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.