PostgreSQL整型int與布林boolean的自動轉換設定(含自定義cast與cast規則介紹)

標籤

PostgreSQL , cast , 資料型別轉換 , 自動型別轉換 , 隱式轉換 , 顯示轉換 , 整型 , 布林 , int , boolean

---

背景

在使用資料庫時，經常會遇到一些因為客戶端輸入的型別與資料庫定義的型別不匹配導致的錯誤問題。

例如資料庫定義的是布林型別，而輸入的是整型：

postgres=# create table cas_test(id int, c1 boolean);  
CREATE TABLE  
  
postgres=# set VERBOSITY verbose  
postgres=# insert into cas_test values (1, int `1`);  
ERROR:  42804: column "c1" is of type boolean but expression is of type integer  
LINE 1: insert into cas_test values (1, int `1`);  
                                            ^  
HINT:  You will need to rewrite or cast the expression.  
LOCATION:  transformAssignedExpr, parse_target.c:591  

又或者資料庫定義的是時間，使用者輸入的是字串：

postgres=# create table tbl123(id int, crt_time timestamp);  
CREATE TABLE  
  
postgres=# insert into tbl123 values (1, text `2017-01-01 10:00:00`);  
ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  
LINE 1: insert into tbl123 values (1, text `2017-01-01 10:00:00`);  
                                           ^  
HINT:  You will need to rewrite or cast the expression.  

從錯誤提示來看，資料庫已經很清晰的告訴你為什麼了。那麼怎麼讓資料庫自動轉換呢？

PostgreSQL有一個語法，支援資料型別的轉換（賦值、引數、表示式 等位置的自動轉換）。

postgres=# h create cast  
Command:     CREATE CAST  
Description: define a new cast  
Syntax:  
CREATE CAST (source_type AS target_type)  
    WITH FUNCTION function_name [ (argument_type [, ...]) ]  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITHOUT FUNCTION  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITH INOUT  
    [ AS ASSIGNMENT | AS IMPLICIT ]  

資料庫內建了很多轉換法則：

postgres=# dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 abstime                     | date                        | date                      | in assignment  
 abstime                     | integer                     | (binary coercible)        | no  
 abstime                     | timestamp without time zone | timestamp                 | yes  
 ........  
 integer                     | boolean                     | bool                      | no  

型別的自動轉換實際上也是有一定的規則的，例如 賦值、引數 算是兩種規則。具體含義見如下文件：

《PostgreSQL 自定義自動型別轉換(CAST)》

我們看到整型轉布林是有內建的轉換規則的，那麼為什麼沒有自動轉呢？

postgres=# dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 integer                     | boolean                     | bool                      | no  

和自動轉換的規則有關，no表示不會自動轉換，只有當我們強制指定轉換時，才會觸發轉換的動作：

postgres=# select cast ((int `1`) as boolean);  
 bool   
------  
 t  
(1 row)  

pg_cast裡面的context轉換為可讀的內容(e表示no, a表示assignment, 否則表示implicit)

如果讓資料庫賦值時自動將字串轉換為時間，自動將整型轉換為布林

1、如果資料庫已經內建了轉換規則，那麼可以通過更新系統表的方式，修改自動轉換規則。

例如，將這個INT轉BOOLEAN的規則，修改為assignment的規則。

postgres=# update pg_cast set castcontext=`a` where castsource =`integer`::regtype and casttarget=`boolean`::regtype;  
UPDATE 1  

修改後，我們再檢視這個轉換規則，就變成這樣了

dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 integer                     | boolean                     | bool                      | in assignment  

現在你可以將int自動寫入為BOOLEAN了。

postgres=# create table cas_test(id int, c1 boolean);  
CREATE TABLE  
postgres=# insert into cas_test values (1, int `1`);  
INSERT 0 1  

2、如果系統中沒有兩種型別轉換的CAST規則，那麼我們需要自定義一個。

例如

  
postgres=# create cast (text as timestamp) with inout as ASSIGNMENT;  
CREATE CAST  
  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 text                        | timestamp without time zone | (binary coercible)        | in assignment  

這樣就可以自動將TEXT轉換為TIMESTAMP了。

postgres=# insert into tbl123 values (1, text `2017-01-01 10:00:00`);  
INSERT 0 1  
postgres=# select * from tbl123;  
 id |      crt_time         
----+---------------------  
  1 | 2017-01-01 10:00:00  
(1 row)  

刪掉這個轉換，就會報錯。

postgres=# drop cast (text as timestamp);  
DROP CAST  
postgres=# insert into tbl123 values (1, text `2017-01-01 10:00:00`);  
ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  
LINE 1: insert into tbl123 values (1, text `2017-01-01 10:00:00`);  
                                           ^  
HINT:  You will need to rewrite or cast the expression.  

3、如果沒有內建的轉換函式，我們可能需要自定義轉換函式來支援這種轉換。

例子

自定義一個函式，用於輸入TEXT，返回TIMESTAMPTZ

postgres=# create or replace function cast_text_to_timestamp(text) returns timestamptz as $$  
  select to_timestamp($1, `yyyy-mm-dd hh24:mi:ss`);  
$$ language sql strict ;  
CREATE FUNCTION  

建立規則

postgres=# create cast (text as timestamptz) with function cast_text_to_timestamp as ASSIGNMENT;  
CREATE CAST  
  
postgres=# dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 text                        | timestamp with time zone    | cast_text_to_timestamp    | in assignment  

現在，輸入TEXT，就可以自定轉換為timestamptz了。

postgres=# create table tbl1234(id int, crt_time timestamptz);  
CREATE TABLE  
postgres=# insert into tbl1234 values (1, text `2017-01-01 10:10:10`);  
INSERT 0 1  

當然，這些型別實際上內部都有內部的儲存格式，大多數時候，如果儲存格式通用，就可以直接使用INOUT來轉換，不需要寫轉換函式。

僅僅當兩種型別在資料庫的內部儲存格式不一樣的時候，需要顯示的寫函式來轉換。

參考

《PostgreSQL 自定義自動型別轉換(CAST)》