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 型態的欄位,在我們的系統中,做一些用途.