Oracle vs PostgreSQL，研發(fā)注意事項（7）- 類型轉(zhuǎn)換

本節(jié)以數(shù)值型相互轉(zhuǎn)換以及數(shù)值型和字符型的轉(zhuǎn)換為例大體介紹了Oracle和PostgreSQL類型轉(zhuǎn)換上的部分異同，可據(jù)此思路推廣到其他類型。

一、數(shù)值類型轉(zhuǎn)換

下面以數(shù)值類型為例子說明,包括運算結(jié)果的轉(zhuǎn)換和強制類型轉(zhuǎn)換.
運算結(jié)果
以除運算為例說明.
PostgreSQL的除運算

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

Oracle的除運算

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

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

兩個整型值1和4參與除法運算,結(jié)果PostgreSQL為整型的0,Oracle為浮點型的0.25,兩者的行為不一致.
為何PostgreSQL執(zhí)行整型運算返回的結(jié)果是整型?當(dāng)然,這是PG的機制(整型/整型=整型)使然,在PG中,運算的結(jié)果類型可查詢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個字節(jié)的整型,通過select * from pg_type where oid=21查詢可得)
oprright     | 21 -->同上
oprresult    | 21 -->整型/整型,結(jié)果也是整型
oprcom       | 0
oprnegate    | 0
oprcode      | int2div
oprrest      | -
oprjoin      | -

在PostgreSQL中,要想獲得0.25的結(jié)果,需要進行轉(zhuǎn)換:

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

二、強制類型轉(zhuǎn)換

以字符型->整型為例說明.
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 -->可變長字符型轉(zhuǎn)換為整型
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,整型不能轉(zhuǎn)換為字符型,而Oracle可以.
PG可以通過顯式類型轉(zhuǎn)換或者自定義類型轉(zhuǎn)換的機制實現(xiàn)字符型->整型的轉(zhuǎn)換:

-- 顯式轉(zhuǎn)換
testdb=# select * from t_cast where c_s = 1::varchar;
 c_int | c_s 
-------+-----
     1 | 1
(1 row)
-- 自定義類型轉(zhuǎn)換
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)

通過數(shù)據(jù)字典表pg_cast可查詢PG支持的類型轉(zhuǎn)換.

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 自定義自動類型轉(zhuǎn)換(CAST)