Postgresql 的UUID資料型態

Postgresql 12 在前幾天已經Release了.

因為本系列開始撰寫時,還是11版,故文件還是指向到11版,在頁面上可以很方便選擇版本.
可視需要自行查閱符合使用的版本.

https://www.postgresql.org/docs/11/datatype-uuid.html

在文件中可以看到 UUID 是 RFC 4122, ISO/IEC 9834-8:2005 規範.
其實在 8.3 版就可以看到 UUID了.
https://www.postgresql.org/docs/8.3/datatype-uuid.html

透過以下查詢,可以看到 Postgresql 裡面有 UUID datatype.

select pg_catalog.format_type(oid, typtypmod) 
     , pg_catalog.obj_description(oid, 'pg_type') as "description"
  from pg_catalog.pg_type
 where typnamespace::regnamespace::text = 'pg_catalog' 
   and typname = 'uuid';

+-------------+---------------+
| format_type |  description  |
+-------------+---------------+
| uuid        | UUID datatype |
+-------------+---------------+

Postgresql 有 UUID,有人感嘆減少了他的工作量:

Postgresql 需要安裝 extension, 用來產生 UUID.

create extension "uuid-ossp";

安裝以後,可以產生數種版本的 uuid.

select uuid_generate_v1() v1
     , uuid_generate_v1mc() v1mc
     , uuid_generate_v4() v4
 from generate_series(1,3); 

+-[ RECORD 1 ]--------------------------------+
| v1   | 13cc0344-e8cd-11e9-823a-a4d18cd51174 |
| v1mc | 13cc03a8-e8cd-11e9-823a-23baf0e3b2d6 |
| v4   | 35be7c40-5a98-457c-bccb-d2dbd8da1cb8 |
+-[ RECORD 2 ]--------------------------------+
| v1   | 13cc040c-e8cd-11e9-823a-a4d18cd51174 |
| v1mc | 13cc0420-e8cd-11e9-823a-9fdf39193e96 |
| v4   | 24acd095-6ab5-4ade-8953-94f6d14218d1 |
+-[ RECORD 3 ]--------------------------------+
| v1   | 13cc045c-e8cd-11e9-823a-a4d18cd51174 |
| v1mc | 13cc0470-e8cd-11e9-823a-a7053026769b |
| v4   | ae5b3e33-8176-45f0-bf91-5ddaef367637 |
+------+--------------------------------------+

跟網路有關的,這算是本行了...

select uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org') as "url"
     , uuid_generate_v3(uuid_ns_dns(), 'www.postgresql.org') as "dns"
     , uuid_generate_v3(uuid_ns_oid(), '1.3.6.1.2.1.1') as "snmp_oid";

+-[ RECORD 1 ]------------------------------------+
| url      | cf16fe52-3365-3a1f-8572-288d8d2aaa46 |
| dns      | 9a0d5f51-76ff-394e-ba97-b28a9ff12209 |
| snmp_oid | 703270d8-feea-39ac-8d87-f1bb47fb73a9 |
+----------+--------------------------------------+

既然有了函數可以產生 UUID, 在建立 Table 時,就可以當 default 了,
來看以下的例子:

create table ithelp191007a (
  id integer generated always as identity primary key
, uuid1 uuid not null default uuid_generate_v1mc()
, uuid4 uuid not null default uuid_generate_v4()
, num int null
, its timestamptz not null default clock_timestamp()
);


insert into ithelp191007a(num)
select round(random()*100)
returning (id, uuid1, uuid4, its);

+---------------------------------------------------------------------------------------------------------------+
|                                                      row                                                      |
+---------------------------------------------------------------------------------------------------------------+
| (1,fe943484-e8d0-11e9-823a-2357e0f57636,71edf463-3ef2-4c99-b462-35df70bd1270,"2019-10-07 15:06:33.347809+08") |
+---------------------------------------------------------------------------------------------------------------+
(1 row)

注意到我們使用了 returning .

這樣就很方便的可以讓 uuid 型態的欄位,在我們的系統中,做一些用途.