当前环境
PG14.8 +LINUX 8.8
存在问题：
开发人员问，PG中，支持 生成UUID吗，具体是什么，答，类似这个函数 uuid_generate_v4()
看了一下， select uuid_generate_v4();会报错，找不到该函数
原来postgresql 14 默认是没有该功能，但是可以通过扩展uuid-ossp来实现
解决办法
由于PG是通过源码安装的，
源码中已经有这个源码包了，不需要到处去找
位置在：
/postgresql/soft/postgresql-14.8/contrib/uuid-ossp
1.检查是否已安装扩展uuid-ossp
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
可以看到postgresql目前没有安装uuid-ossp扩展。
2.检查是否有可用安装的扩展UUID-OSSP
--查看当前可用的扩展
postgres=# select * from pg_available_extensions;
name | default_version | installed_version | comment
--------------------+-----------------+-------------------+------------------------------------------------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plperl | 1.0 | | PL/Perl procedural language
plperlu | 1.0 | | PL/PerlU untrusted procedural language
plpython2u | 1.0 | | PL/Python2U untrusted procedural language
plpythonu | 1.0 | | PL/PythonU untrusted procedural language
adminpack | 2.1 | | administrative functions for PostgreSQL
amcheck | 1.3 | | functions for verifying relation integrity
bloom | 1.0 | | bloom access method - signature file based index
btree_gin | 1.3 | | support for indexing common datatypes in GIN
btree_gist | 1.6 | | support for indexing common datatypes in GiST
citext | 1.6 | | data type for case-insensitive character strings
cube | 1.5 | | data type for multidimensional cubes
dblink | 1.2 | | connect to other PostgreSQL databases from within a database
dict_int | 1.0 | | text search dictionary template for integers
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth
file_fdw | 1.0 | | foreign-data wrapper for flat file access
fuzzystrmatch | 1.1 | | determine similarities and distance between strings
hstore | 1.8 | | data type for storing sets of (key, value) pairs
intagg | 1.1 | | integer aggregator and enumerator (obsolete)
intarray | 1.5 | | functions, operators, and index support for 1-D arrays of integers
isn | 1.2 | | data types for international product numbering standards
lo | 1.1 | | Large Object maintenance
ltree | 1.2 | | data type for hierarchical tree-like structures
old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold
pageinspect | 1.9 | | inspect the contents of database pages at a low level
pg_buffercache | 1.3 | | examine the shared buffer cache
pg_freespacemap | 1.2 | | examine the free space map (FSM)
seg | 1.4 | | data type for representing line segments or floating-point intervals
pg_prewarm | 1.2 | | prewarm relation data
pg_stat_statements | 1.9 | | track planning and execution statistics of all SQL statements executed
pg_surgery | 1.0 | | extension to perform surgery on a damaged relation
pg_trgm | 1.6 | | text similarity measurement and index searching based on trigrams
pgcrypto | 1.3 | | cryptographic functions
pgrowlocks | 1.2 | | show row-level locking information
pgstattuple | 1.5 | | show tuple-level statistics
pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info
postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers
autoinc | 1.0 | | functions for autoincrementing fields
insert_username | 1.0 | | functions for tracking who changed a table
moddatetime | 1.0 | | functions for tracking last modification time
refint | 1.0 | | functions for implementing referential integrity (obsolete)
tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
tcn | 1.0 | | Triggered change notifications
tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent | 1.1 | | text search dictionary that removes accents
sslinfo | 1.2 | | information about SSL certificates
xml2 | 1.1 | | XPath querying and XSLT
bool_plperlu | 1.0 | | transform between bool and plperlu
bool_plperl | 1.0 | | transform between bool and plperl
hstore_plperl | 1.0 | | transform between hstore and plperl
hstore_plperlu | 1.0 | | transform between hstore and plperlu
jsonb_plperlu | 1.0 | | transform between jsonb and plperlu
jsonb_plperl | 1.0 | | transform between jsonb and plperl
hstore_plpythonu | 1.0 | | transform between hstore and plpythonu
hstore_plpython2u | 1.0 | | transform between hstore and plpython2u
hstore_plpython3u | 1.0 | | transform between hstore and plpython3u
jsonb_plpythonu | 1.0 | | transform between jsonb and plpythonu
jsonb_plpython2u | 1.0 | | transform between jsonb and plpython2u
jsonb_plpython3u | 1.0 | | transform between jsonb and plpython3u
ltree_plpythonu | 1.0 | | transform between ltree and plpythonu
ltree_plpython2u | 1.0 | | transform between ltree and plpython2u
ltree_plpython3u | 1.0 | | transform between ltree and plpython3u
(64 rows)
可以看到postgres目前并没有可用的uuid-ossp扩展。
此时，直接创建uuid-ossp会报错，如：
postgres=# create extension "uuid-ossp";
ERROR: could not open extension control file "/postgres/pg14/share/extension/uuid-ossp.control": No such file or directory
postgres=#
注意：
要用双引号将uuid-ossp引起来，因为有个中划线“-”。
3.PG安装UUID选项
注意：以ROOT用户去编译
su - root
cd /postgresql/soft/postgresql-14.8/
./configure --prefix=/postgresql/pg14 --with-uuid=ossp #prefix 安装目录
该操作只是在已安装完PG后，把uuid-ossp编译安装进了PG，不影响现有库。
4.源码编译UUID
#进入扩展目录
# cd /postgresql/soft/postgresql-14.8/contrib/uuid-ossp
#编译安装
# make && make install
5.配置UUID的软链接
--这里需要创建软连接，否则，后面创建扩展，会报错 ：找不到 /postgresql/pg14/lib/libuuid.so.16
# find / -name libuuid.so.16
/postgresql/soft/uuid/uuid-1.6.2/.libs/libuuid.so.16
# ln -s /postgresql/soft/uuid/uuid-1.6.2/.libs/libuuid.so.16 /postgresql/pg14/lib/
6.创建扩展
查看可用扩展
postgres=# select * from pg_available_extensions;
...
uuid-ossp | 1.1 | | generate universally unique identifiers
可以看到已经有扩展uuid-ossp了。下面可以创建了。
创建扩展
postgres=# create extension "uuid-ossp";
CREATE EXTENSION
7.使用UUID
安装扩展成功以后，就可以使用函数uuid_generate_v4()来生产uuid了。
postgres=# select uuid_generate_v4();
uuid_generate_v4
--------------------------------------
9ef673d2-3652-455c-8b6e-c634165f514b
(1 row)
可以看到，已经安装好了
-------------------------------------------
为了方便交流，我建立了一个微信群：水煮数据库，主要交流日常运维中用到的数据库相关问题，包含但不限于：ORACLE,PG，MYSQL，SQLSERVER，OB,TIDB，达梦，TDSQL，OPENGAUSS，人大金仓，GBASE等等，如果有兴趣交流，可以加我微信：zq24803366, 我可以拉你入群。
————————————————
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/cqsztech/article/details/138426614
