Oracle vs PostgreSQL，研發注意事項（7）- 型別轉換

本節以數值型相互轉換以及數值型和字元型的轉換為例大體介紹了Oracle和PostgreSQL型別轉換上的部分異同，可據此思路推廣到其他型別。

一、數值型別轉換

下面以數值型別為例子說明,包括運算結果的轉換和強制型別轉換.
運算結果
以除運算為例說明.
PostgreSQL的除運算

testdb=# select 1/4;
 ?column? 
----------
        0
(1 row)

Oracle的除運算

TEST-orcl@server4>select 1/4 from dual;

       1/4
----------
       .25

兩個整型值1和4參與除法運算,結果PostgreSQL為整型的0,Oracle為浮點型的0.25,兩者的行為不一致.
為何PostgreSQL執行整型運算返回的結果是整型?當然,這是PG的機制(整型/整型=整型)使然,在PG中,運算的結果型別可查詢pg_operator獲得:

testdb=# \x
Expanded display is on.
testdb=# select * from pg_operator where oprname = '/' and oprleft=21 and oprright = 21;
-[ RECORD 1 ]+--------
oprname      | / -->運算子
oprnamespace | 11
oprowner     | 10
oprkind      | b
oprcanmerge  | f
oprcanhash   | f
oprleft      | 21 -->int2(佔用2個位元組的整型,透過select * from pg_type where oid=21查詢可得)
oprright     | 21 -->同上
oprresult    | 21 -->整型/整型,結果也是整型
oprcom       | 0
oprnegate    | 0
oprcode      | int2div
oprrest      | -
oprjoin      | -

在PostgreSQL中,要想獲得0.25的結果,需要進行轉換:

testdb=# select 1/4::float;
 ?column? 
----------
     0.25
(1 row)

二、強制型別轉換

以字元型->整型為例說明.
PostgreSQL

testdb=# drop table if exists t_cast ;
DROP TABLE
testdb=# create table t_cast (c_int int,c_s varchar(20));
CREATE TABLE
testdb=# insert into t_cast values(1,'1');
INSERT 0 1
testdb=# insert into t_cast values(2,'2');
INSERT 0 1
testdb=# select * from t_cast where c_int = 1;
 c_int | c_s 
-------+-----
     1 | 1
(1 row)

testdb=# select * from t_cast where c_s = 1;
ERROR:  operator does not exist: character varying = integer -->可變長字元型轉換為整型
LINE 1: select * from t_cast where c_s = 1;
                                       ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Oracle

TEST-orcl@server4>drop table t_cast;

Table dropped.

TEST-orcl@server4>create table t_cast (c_int int,c_s varchar2(20)) tablespace users;

Table created.

TEST-orcl@server4>insert into t_cast values(1,'1');

1 row created.

TEST-orcl@server4>insert into t_cast values(2,'2');

1 row created.

TEST-orcl@server4>select * from t_cast where c_int = 1;

     C_INT C_S
---------- --------------------
         1 1

TEST-orcl@server4>select * from t_cast where c_s = 1;

     C_INT C_S
---------- --------------------
         1 1

PG,整型不能轉換為字元型,而Oracle可以.
PG可以透過顯式型別轉換或者自定義型別轉換的機制實現字元型->整型的轉換:

-- 顯式轉換
testdb=# select * from t_cast where c_s = 1::varchar;
 c_int | c_s 
-------+-----
     1 | 1
(1 row)
-- 自定義型別轉換
testdb=# create cast(varchar as integer) with inout as implicit;
CREATE CAST
testdb=# select * from t_cast where c_s = 1;
 c_int | c_s 
-------+-----
     1 | 1
(1 row)

透過資料字典表pg_cast可查詢PG支援的型別轉換.

testdb=# select oid,a.* from pg_cast a where castsource=1043 and casttarget = 23;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod 
-------+------------+------------+----------+-------------+------------
 16774 |       1043 |         23 |        0 | i           | i --> 這是新加的記錄

三、參考資料

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