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:
1 | psql postgres |
- Next, use
CREATEsyntax to install the extensionuuid-ossp:
1 | 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:
1 | CREATE EXTENSION |
- Confirm that the UUID Generator in PostgreSQL
uuid-osspwas installed by printing out a list of all the extensions that are available on the server:
1 | SELECT * FROM pg_available_extensions; |
- You should see a response similar to this one:
1 2 3 4 5 6 | 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
\dfcommand 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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
SELECTclause to generate a UUID:
1 | SELECT uuid_generate_v4(); |
- The result should be similar to one like this one below:
1 2 3 4 5 | 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_v4like this:
1 2 3 4 5 | 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