Postgresql 的字元資料型態 

今天來探討 Postgresql 的字串資料型態,相信此資料型態為大家所熟悉.
但是 Postgresql 在 ANSI SQL 的標準之上,做了些加強.

首先是文件
https://docs.postgresql.tw/the-sql-language/data-types/character-types

接著來看一些測試

select octet_length('abcde'::varchar(1)) as "vc_1"
     , octet_length('abcde'::varchar(5)) as "vc_5"
     , octet_length('abcde'::varchar(10)) as "vc_10"
     , octet_length('abcde'::char(1)) as "c_1"
     , octet_length('abcde'::char(5)) as "c_5"
     , octet_length('abcde'::char(10)) as "c_10";

+------+------+-------+-----+-----+------+
| vc_1 | vc_5 | vc_10 | c_1 | c_5 | c_10 |
+------+------+-------+-----+-----+------+
|    1 |    5 |     5 |   1 |   5 |   10 |
+------+------+-------+-----+-----+------+

octet_length() 回報 string 使用的 bytes.
觀察上面的結果,可以知道 char(n) 型態,會浪費空間.

char(n) , varchar(n) 相信大家應該很熟悉了.
但是在 Postgresql 中並不建議使用,會保留主要是為了跟 ANSI SQL 標準相容,
當我們執行由其他資料庫匯出的 SQL Command 時,可以正常使用.

觀察以下這段 SQL Command 執行結果.

create table ithelp190923 (
  id int generated always as identity primary key
, vstr varchar not null
, txt text not null
);

commit;

insert into ithelp190923 (vstr, txt)
select rpad('a', 5001, 'pad'), rpad('a', 5001, 'pad');

commit;

select length(vstr) as "vstr_lngth"
     , octet_length(vstr) as "vstr_bytes"
     , length(txt) as "vstr_lngth"
     , octet_length(txt) as "vstr_bytes"
  from ithelp190923;

+------------+------------+------------+------------+
| vstr_lngth | vstr_bytes | vstr_lngth | vstr_bytes |
+------------+------------+------------+------------+
|       5001 |       5001 |       5001 |       5001 |
+------------+------------+------------+------------+

可以發現使用了 varchar , text. 而且都有很大的容量.

實際上在 Postgresql 中是以 text 為主要儲存的方式.而且容量達到 1GB.

varchar, text 在平常的應用中,提供很大的彈性.
以敝人以往的經驗,下載一些資料,是CSV格式的,要倒入資料庫中,最麻煩的就是
欄位長度的設定,往往需要反覆修改,耗時且繁瑣.當使用 Postgresql text 
型態,就很方便的倒入.這時候再計算各欄位最大長度,若需要轉到其他種資料庫,
由 Postgresql 來做前置處理也是一個好方法.

但是這種無截位的方式,也會來一些不便.當我們需要對輸入的資料做長度的限制時,
該如何處理?

在 Postgresql 有 ANSI SQL 的 Check
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

來看以下的例子

create table ithelp190923a (
  id int generated always as identity primary key
, txt1 text not null check (length(txt1) <= 5)
, txt2 text not null check (txt2 ~ '^[[:alpha:]]{4}$')
);

commit;

insert into ithelp190923a (txt1, txt2) values
('abcde', 'abcd');

commit;

Postgresql 提供了 ANSI SQL 標準的 check, 搭配使用,會比 varchar(n)
的方式更靈活.
check 在之後的探討中,也會活躍的出現.

今天就先把最基本的字元資料型態,就 Postgresql 與其他資料庫產品比較特別需要注意
的地方,提出來供大家參考.至於相關函數等等,請參考

https://docs.postgresql.tw/the-sql-language/functions-and-operators/string-functions-and-operators