UUID Generator in PostgreSQL

Introduction

As a DBA, you must do what you can to secure your company’s database records. Client data, proprietary information, and employee records are all sensitive. Managing security levels can take some time to do though, especially if you have multiple servers. Luckily, UUID generator in PostgreSQL is available to help you avoid data duplication and make your scripting easier.

Prerequisites

  • For your OS, install the correct edition of PostgreSQL. Configure it, and then run the program.

The advantages of UUID

UUID Generator in PostgreSQL offers many conveniences for DBAs:

  • Database row consolidation is possible due to the unique values of UUIDs.

  • In a multiple server environment, each server will have its own UUID.

  • Data is more secure when a URL is used in a browser’s address bar.

  • UUIDs built-in security enhancements enable primary keys to be more hacker-proof

Install the UUID-OSSP extension to generate UUIDs

  • Make a psql PostgreSQL server connection using the command below:
psql postgres
  • Next, use CREATE syntax to install the extension uuid-ossp:
CREATE extension IF NOT EXISTS "uuid-ossp";
  • Because you have the “IF NOT EXISTS” function in your statement above for the extension uuid-ossp , if the extension isn’t on the server, it will create one:
CREATE EXTENSION
  • Confirm that the UUID Generator in PostgreSQL uuid-ossp was installed by printing out a list of all the extensions that are available on the server:
SELECT * FROM pg_available_extensions;
  • You should see a response similar to this one:
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+----------------------------------------------------------------------
refint | 1.0 | | functions for implementing referential integrity (obsolete)
unaccent | 1.1 | | text search dictionary that removes accents
btree_gin | 1.3 | | support for indexing common datatypes in GIN
uuid-ossp | 1.1 | 1.1 | generate universally unique identifiers (UUIDs)

Note that the extension uuid-ossp version 1.1 is listed last, so it was indeed installed on the server successfully.

Generate a UUID with the extension uuid-ossp

  • Use the \df command to display all of the functions availalble. The correct command to generate a UUID should be on the list. You should see a response that resembles something like this:
List OF functions
Schema | Name | RESULT DATA TYPE | Argument DATA types | TYPE
--------+--------------------+------------------+---------------------------+------
public | uuid_generate_v1 | uuid | | func
public | uuid_generate_v1mc | uuid | | func
public | uuid_generate_v3 | uuid | namespace uuid, name text | func
public | uuid_generate_v4 | uuid | | func
public | uuid_generate_v5 | uuid | namespace uuid, name text | func
public | uuid_nil | uuid | | func
public | uuid_ns_dns | uuid | | func
public | uuid_ns_oid | uuid | | func
public | uuid_ns_url | uuid | | func
public | uuid_ns_x500 | uuid | | func
(10 ROWS)

UUID Generator in PostgreSQL

The example functions list result shows a total of 10 rows, with each row listing a function. The fourth function on the list is the latest version, uuid_generate_v4, for random UUID generation.

  • Use the SELECT clause to generate a UUID:
SELECT uuid_generate_v4();
  • The result should be similar to one like this one below:
postgres=# SELECT uuid_generate_v4();
uuid_generate_v4
--------------------------------------
a5e3c008-85ba-4d65-aa15-c9a57bf85359
(1 ROW)

You can use the command uuid_generate_v4 again to produce a new UUID, and it will return a response of a different UUID every time.

  • Try it. Use the same command uuid_generate_v4 like this:
postgres=# SELECT uuid_generate_v4();
uuid_generate_v4
--------------------------------------
9dec998c-2b49-4cbd-8580-32f4f4735b63
(1 ROW)

As shown above, another successful generation of a new UUID is the result.

Conclusion

This tutorial showed you how to use the UUID generator in PostgreSQL. The extension uuid-ossp helps DBAs secure multiple servers with global one-of-a-kind identifiers. Although the extension is a component of PostgreSQL application, you will likely need to install it with the CREATE syntax.

With the \df command, it’s easy to display a list of extensions to find out exactly which version of uuid-osspis installed on your operating system. This way, you can be sure to create UUIDs for your applications expeditiously. Take advantage of PostgreSQL’s built-in UUID generator today.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.