---
# System prepended metadata

title: PostgreSQL extension periods 介紹

---

# PostgreSQL extension periods 介紹

ANSI SQL 2011 裡引進了 periods and tables with SYSTEM VERSIONING 的觀念,後來在ANSI SQL 2016中更進一步.
目前各主流RDBMS,有程度不一的支持與進展.
PostgreSQL 方面目前有 periods 這個 extension,提供了資料型態與函數等相結合的支持.

### 網站
[github](https://github.com/xocolatl/periods)

## 安裝
```shell=
git clone https://github.com/xocolatl/periods
cd periods
make
make install
```

```sql=
create extension periods with schema public;

commit;
```

安裝後會建立 periods 這個 schema, 若之前未安裝 btree_gist extension, 可以用
```sql
create extension periods with schema public cascade;
```
這樣會一併安裝.

## Period 介紹

A period is a definition on a table which specifies a name and two columns. The period’s name cannot be the same as any column name of the table.

其實就是另外兩個日期時間欄位整合起來的欄位.在PG中有 daterange 這類的資料型態,我們先往下看.

常見的用處例如 例如民宿預約,PG原本有 btree_gist , 使用 exclude 
類似這樣的語法
EXCLUDE USING gist (id WITH =, valid WITH &&)

實際也是使用這樣的方式,只是封裝為 period.符合ANSI SQL規定.

若使用ANSI SQL 標準語法應該是這樣
```sql=
create table room_booking (
  guest text not null
, room_number int not null
, checkin date not null
, checkout date not null
, period for stay (checkin, checkout)
);
```

目前PG這樣是無法直接執行的! 因為 periods extension 尚未整併為語法,要用間接的方式.

```sql=
create table room_booking (
  guest text not null
, room_number int not null
, checkin date not null
, checkout date not null
);

select periods.add_period('room_booking', 'stay', 'checkin', 'checkout');

--建立 Unique constraints

select periods.add_unique_key('room_booking', ARRAY['room_number'], 'stay');
        add_unique_key
-------------------------------
 room_booking_room_number_stay
 
commit;
```

### 測試
```sql=
insert into room_booking values
('Alice', 112, '2021-05-10', '2021-05-14');

commit;

insert into room_booking values
('Bob', 112, '2021-05-13', '2021-05-15');

ERROR:  23P01: conflicting key value violates exclusion constraint "room_booking_room_number_daterange_excl"
DETAIL:  Key (room_number, daterange(checkin, checkout, '[)'::text))=(112, [2021-05-13,2021-05-15)) conflicts with existing key (room_number, daterange(checkin, checkout, '[)'::text))=(112, [2021-05-10,2021-05-14)).
CONSTRAINT NAME:  room_booking_room_number_daterange_excl
```

可以看到這樣就不能overlap !

## SYSTEM-VERSIONED TABLES
這是主要要介紹的功能.直接來看實際的例子吧!

```sql=
create table sysver (
  val text
, flap boolean
);

select periods.add_system_time_period('sysver', excluded_column_names => ARRAY['flap']);

-- flap 欄位會排除,不做紀錄.

select periods.add_system_versioning('sysver',
    history_table_name => 'sysver_history',
    view_name => 'sysver_view',
    function_as_of_name => 'sysver_as_of',
    function_between_name => 'sysver_between',
    function_between_symmetric_name => 'sysver_between_symmetric',
    function_from_to_name => 'sysver_from_to');

NOTICE:  00000: history table "sysver_history" created for "sysver", be sure to index it properly
LOCATION:  exec_stmt_raise, pl_exec.c:3889
 add_system_versioning
-----------------------

(1 row)

commit;
```

這樣就建立的 sysver 的 history table, sysver_history,
還有一些函數.來看實際的應用吧.

```sql=
insert into sysver (val, flap) values ('hello', false);

commit;

select *
  from sysver;

  val  | flap |       system_time_start       | system_time_end
-------+------+-------------------------------+-----------------
 hello | f    | 2021-05-22 15:11:02.689252+08 | infinity
 
select *
  from sysver_history;

 val | flap | system_time_start | system_time_end
-----+------+-------------------+-----------------
(0 rows)
```
目前很單純的輸入一筆.

```sql=
update sysver
   set val = 'world';

commit;

select *
  from sysver;

  val  | flap |       system_time_start       | system_time_end
-------+------+-------------------------------+-----------------
 world | f    | 2021-05-22 15:15:55.621058+08 | infinity

select *
  from sysver_history;

  val  | flap |       system_time_start       |        system_time_end
-------+------+-------------------------------+-------------------------------
 hello | f    | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
 
```

update 以後,觀察 sysver 與 sysver_history 的內容.
接著改變幾次flap,觀察sysver_history 會不會受到影響?

```sql=
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
update sysver set flap = not flap;
commit;

select *
  from sysver;

  val  | flap |       system_time_start       | system_time_end
-------+------+-------------------------------+-----------------
 world | t    | 2021-05-22 15:15:55.621058+08 | infinity

select *
  from sysver_history;

  val  | flap |       system_time_start       |        system_time_end
-------+------+-------------------------------+-------------------------------
 hello | f    | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
 
```

接著做 delete

```sql=
delete from sysver;

select val
  from sysver;

 val
-----
(0 rows)

select *
  from sysver_history
 order by system_time_start;

  val  | flap |       system_time_start       |        system_time_end
-------+------+-------------------------------+-------------------------------
 hello | f    | 2021-05-22 15:11:02.689252+08 | 2021-05-22 15:15:55.621058+08
 world | t    | 2021-05-22 15:15:55.621058+08 | 2021-05-22 15:23:07.876518+08
(2 rows)
```

這樣目前 sysver 及 sysver_history 都可以觀察到.接著來做
temporal queries, 目前還未支持語法,只能先用 function的方式.

```sql=
select val
  from sysver_as_of('2021-05-22 15:12:02+08')
 order by system_time_start;

  val
-------
 hello

select val
  from sysver_as_of('2021-05-22 15:15:56+08')
 order by system_time_start;

  val
-------
 world

select val 
  from sysver_between('2021-05-22 15:12:02+08', '2021-05-22 15:15:56+08') 
 order by system_time_start;

  val
-------
 hello
 world
(2 rows)

select val 
  from sysver_between('2021-05-22 15:15:56+08', '2021-05-22 15:12:02+08') 
 order by system_time_start;

 val
-----
(0 rows)

select val 
  from sysver_from_to('2021-05-22 15:12:02+08', '2021-05-22 15:15:56+08') 
 order by system_time_start;
 
  val
-------
 hello
 world
(2 rows)
```

## 結語
今天先介紹 periods 這個 extension, 比之前的 extension temporal_tables 有更高的整體性,也更貼近 ANSI SQL 的規定.
相信在不久的將來,大家可以很方便使用ANSI SQL 語法來做查詢.
