Introduction to PostgreSQL uuid Data Type

The uuid data type in PostgreSQL is used to store 128-bit globally unique identifiers (UUIDs), also known as GUIDs (Globally Unique Identifiers). UUIDs are a standard format used to identify entities in a computer system, such as software, hardware, or files. UUIDs are widely used in distributed systems to ensure uniqueness, and they have a very high probability of not colliding with other UUIDs regardless of where and when they are generated. In PostgreSQL, UUIDs can be used as primary keys, foreign keys, or in any other situation where a unique identifier is needed.

Syntax

In PostgreSQL, the uuid data type is used when creating columns or variables of type uuid. The following is an example SQL statement for creating a uuid column:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

Use Cases

The uuid data type is particularly useful in the following scenarios:

• When a globally unique identifier needs to be ensured, such as for database primary keys, filenames, URLs, identifiers in message queues, etc.
• When unique identifiers are needed in a distributed system.
• When synchronizing data across multiple databases to avoid primary key conflicts.

Examples

Example 1

Suppose we have a table named users, and we want to generate a globally unique identifier for each user as the primary key. We can use the uuid data type to achieve this. For example:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

We can insert some data into the users table:

INSERT INTO users (id, name, email) VALUES
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Alice', '[email protected]'),
('3c8d4435-1612-4125-8443-6031c534c069', 'Bob', '[email protected]'),
('7f9c7985-4e38-4b4e-b16f-02bce67a7f6f', 'Charlie', '[email protected]');

Query the data in the users table:

SELECT * FROM users;

The result is as follows:

 id                 |  name   |        email
--------------------------------------+---------+---------------------
 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | Alice   | [email protected]
 3c8d4435-1612-4125-8443-6031c534c069 | Bob     | [email protected]
 7f9c7985-4e38-4b4e-b16f-02bce67a7f6f | Charlie | [email protected]
(3 rows)

Example 2

Assuming we have a table named products where each product has a globally unique identifier. We can use the uuid data type to store these identifiers. For example:

CREATE TABLE products (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

We can insert some data into the products table:

INSERT INTO products (id, name, price)
VALUES
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Product 1', 19.99),
    ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'Product 2', 29.99),
    ('6ba7b811-9dad-11d1-80b4-00c04fd430c8', 'Product 3', 39.99);

Query the data in the products table:

SELECT * FROM products;

The result is as follows:

 id                |   name    | price
--------------------------------------+-----------+-------
 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | Product 1 | 19.99
 6ba7b810-9dad-11d1-80b4-00c04fd430c8 | Product 2 | 29.99
 6ba7b811-9dad-11d1-80b4-00c04fd430c8 | Product 3 | 39.99
(3 rows)

In the above example, we use the uuid data type to store product identifiers. This ensures that each product has a globally unique identifier, avoiding duplicates.

Conclusion

The uuid data type is used in PostgreSQL to store globally unique identifiers. Using UUIDs can avoid duplicate identifier issues in a distributed environment and can also avoid performance issues caused by the ordering of identifiers. In practical development, it is important to choose appropriate data types for storing data based on specific requirements and use indexes effectively to improve query efficiency.