Skip to content

📚 uuid-ossp enabled

🔍 Prepare：

## OS and Software environment：

Environment name Version
OS Rocky Linux 9.x
DB PostgreSQL 16.X

🔍 introduction：

Uuid ossp is an extension module in PostgreSQL database used to generate and manipulate UUIDs (Universal Unique Identifier). UUID is a standard 128 bit identifier used to uniquely identify entities in distributed systems.

Here are some explanations about the uuid ossp extension: 1、UUID generation: The uuid ossp extension provides functions to generate UUIDs of different types. This includes version 4 UUIDs generated using random algorithms, version 3 and version 5 UUIDs generated using namespaces and names, and version 1 UUIDs generated using MAC addresses and timestamps. 2、UUID data type: The uuid ossp extension also introduces the UUID data type, allowing UUID values to be stored and processed in the database. The UUID data type is similar in syntax to other basic data types and provides some functions and operators for comparing, manipulating, and converting between UUIDs. 3、UUID index: By creating an index on the UUID column, UUID values can be efficiently retrieved and queried. The uuid ossp extension supports creating B-tree indexes on UUID columns to accelerate the lookup of UUID values. 4、UUID operation functions: The uuid ossp extension also provides functions for performing various operations between UUID values. For example, comparing the size of two UUID values, extracting the components of UUIDs, converting UUIDs to string representations, and so on.

### 1. Create uuid-ossp Extension On postgres DB（If Other DB Need，Please Enter DB Execute）

su - postgres
psql -p 5432 -d postgres

CREATE EXTENSION "uuid-ossp";

alt text

### 2. Use uuid_generate_v4

GRANT USAGE ON SCHEMA public TO "USE_UUID_User";
SELECT uuid_generate_v4();

alt text

alt text

### 3. Create Table and set default column value

alt text

Notice： As the user does not have the permission to create extensions themselves, they require our assistance in executing:

alt text

Due to the possibility of multiple users needing to use this plugin, the postgres account will be used to execute it. The related functions will be stored under the public schema, and users need to be authorized to use the public usage permission and the function uuid_generate-v4() permission. Otherwise, users will encounter the following error when using it:

alt text

GRANT USAGE ON SCHEMA public TO cpbg_dataplatform;
GRANT EXECUTE ON FUNCTION public.uuid_generate_v4() TO PUBLIC;--Because if authorized separately to cpbg_datapplatform here, it will also be implicitly generated and given to PULIC, so it is directly granted to PULIC users.