PostgreSQL UUID Types and Functions

The PostgreSQL UUID, or Universal Unique Identifier, is specified by RFC 4122 and has a 128-bit length. Internal algorithms are used to construct the UUID, and each value is distinct. PostgreSQL has its own UUID data type and generates them with modules. The UUID data type is commonly cast off within distributed databases because this data type ensures singularity, rather than the SERIAL type, which only generates singular values inside a single database. While PostgreSQL allows you to save and contrast UUID values, this one does not have methods for creating them at its core. Rather, it relies on third-party packages that have specific UUID generation algorithms.

Let us now take a look at certain representations of UUID values, as seen in the diagram below. A UUID is made up of 32 bits of hexadecimal digits with up to four hyphens, as seen in the diagram below. A UUID can also have a value of 0, which means that all bits are empty.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image1-18.png" data-lazy- height="87" src="data:image/svg xml,” width=”335″>

Create UIID in PostgreSQL pgAdmin 4 GUI

Open the PostgreSQL ‘pgAdmin 4’ graphical user interface. Next, connect the user ‘postgres’ with the server ‘PostgreSQL 13’ by providing the password for the username. Hit the ‘OK’ button to start using it.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image3-17.png" data-lazy- height="218" src="data:image/svg xml,” width=”602″>

The “uuid-ossp” package will be installed in the ‘test’ Database using the CREATE EXTENSION instruction. If you properly follow the instructions provided below, you will receive the “CREATE EXTENSION” message, as displayed in the image below. In the following command, the “IF NOT EXISTS” condition is cast off, which allows us to stop re-installing the package.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image2-17.png" data-lazy- height="204" src="data:image/svg xml,” width=”501″>

In the ‘Extensions’ option, you can see the newly installed “uuid-ossp” package.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image5-16.png" data-lazy- height="157" src="data:image/svg xml,” width=”395″>

The following is a list of the functions related to the uuid-ossp installed package in the ‘test’ database:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image4-17.png" data-lazy- height="241" src="data:image/svg xml,” width=”468″>

The “uuid_generate_v1()” method can be used to produce UUID values and, depending on the present timestamp, the MAC address collection, which is also an arbitrary value. When the following instruction is run in the query tool area, we will obtain the subsequent output. This will show the UUID value generated by the “uuid_generate_v1()” method, as displayed in the snapshot below.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image7-14.png" data-lazy- height="205" src="data:image/svg xml,” width=”411″>

Create UIID in PostgreSQL pgAdmin 4 GUI

Open your PostgreSQL command-line shell and connect it with the localhost server, or any other server you want to be connected to, by providing its name and hitting Enter. You can change the database by writing its name in the ‘Database’ line. As you can see, we are using the ‘test’ database. You can also change your port number and username to switch. After that, you will be asked for the password for the selected user. Upon providing the password, you will be good to go. If you do not want to make any changes here, then leave the spaces empty.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image6-14.png" data-lazy- height="192" src="data:image/svg xml,” width=”575″>

The “uuid-ossp” bundle can be mounted using the CREATE EXTENSION query. Write the following command in the command shell of PostgreSQL, and you may obtain the subsequent “CREATE EXTENSION” message. We have also cast off the “IF NOT EXISTS” clause, which permits us to halt reinstalling the bundle.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image9-12.png" data-lazy- height="36" src="data:image/svg xml,” width=”429″>

The “uuid_generate_v1()” function will be used to build the UUID values contingent on the current timestamp, MAC address group, and a haphazard value. By executing the following query in the query space, you will obtain the succeeding output, which shows the UUID value created using the “uuid_generate_v1()” method.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image8-12.png" data-lazy- height="86" src="data:image/svg xml,” width=”330″>

The “uuid_generate_v4()” method can be used to create a UUID value exclusively established with haphazard numbers, as shown below. This will produce an output like the following.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image12-9.png" data-lazy- height="91" src="data:image/svg xml,” width=”329″>

Example of PostgreSQL UUID Datatype

This example shows you how the PostgreSQL UIID data type operates. We will use the CREATE command to construct a separate ‘Client’ table, as well as UUID fields. The client id, client first name, client last name, client email id, and client address are only a few of the columns in the ‘Client’ table. We have been using the ‘Client_id’ as the UUID data type, which is also the primary key. The “uuid_generate_v4()” method will also dynamically produce the primary fundamental column values. Try the following CREATE TABLE command in the query tool area to construct a ‘Client’ table in a ‘test’ database where the uuid-ossp module is installed. You will receive the “CREATE TABLE” message, which means that the table has been created properly.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image10-11.png" data-lazy- height="316" src="data:image/svg xml,” width=”516″>

When you add a new line without specifying the ‘Client_id field’ value, PostgreSQL can use the “uuid_generate_v4()” method to produce a ‘Client_id’ value. With the INSERT instruction, we will put several values into it. The following response screen will appear after executing the instruction below, which indicates that the stated values were effectively incorporated into the ‘Client’ table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image11-10.png" data-lazy- height="267" src="data:image/svg xml,” width=”656″>

We will now fetch all the records of the ‘Client’ table using the SELECT query in the query editor tool. You will obtain the following output after executing the instruction provided below, which will show all or most information in the Client table. The ‘Client_id’ field will be occupied by the UUID values generated by the “uuid_generate_v4()” method, as seen in the image below.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image13-8.png" data-lazy- height="292" src="data:image/svg xml,” width=”800″>

Conclusion

This article covered the main points in the PostgreSQL UUID data type portion using the PostgreSQL pgAdmin 4 Graphical User Interface and Command-line shell, including the following:

• The resultant UUID values for a column are stored in the PostgreSQL UUID datatype.
• To build UUID values, the CREATE Extension query must be used to input the uuid-ossp package utility.
• You may need the “uuid_generate_v4()” feature to dynamically extract the UUID results for the table’s specific columns.

Hopefully, you will not run into any problems while working on the UUID types and functions in PostgreSQL.