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:

• Next, use CREATE syntax to install the extension 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:

• 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:

• You should see a response similar to this one:

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:

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:

• The result should be similar to one like this one below:

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:

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.