PostgreSQL 隱式類型轉換探秘

  • 2019 年 11 月 21 日
  • 筆記

個人簡介

何小棟， 從事產品研發和架構設計工作，對Oracle、PostgreSQL有深入研究，ITPUB資料庫版塊資深版主。現就職於廣州雲圖數據技術有限公司，系統架構師，部落格：http://blog.itpub.net/6906/

摘要

本文通過與Oracle隱式類型轉換的比較簡要介紹了PostgreSQL中的隱式類型轉換，包括Oracle隱式類型轉換的機制、PostgreSQL隱式類型轉換的機制和原理。

一、緣起

在進入正題前，我們先看下面一個案例，在psql中執行以下SQL：

— 創建表

testdb=# create table t_cast (id int);

CREATE TABLE

— 插入數據

testdb=# insert into t_cast values(1),(2),(3);

INSERT 0 3

— 查詢數據

testdb=# select * from t_cast where id = '1';

id

—-

1

(1 row)

testdb=# select * from t_cast where id = '1'::text;

psql: ERROR: operator does not exist: integer = text

LINE 1: select * from t_cast where id = '1'::text;

^

HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

數據表t_cast中的id列類型為int，執行第一條SQL沒有出錯，第二條SQL則出錯，錯誤資訊為沒有相匹配的operator，給出的HINT為添加顯式類型轉換。對於熟悉Oracle資料庫的同學來說，不禁會產生疑問：』1』和』1』::text不都是字元類型嗎？兩者不是一回事？而同樣的SQL，在Oracle中則不會報錯，處理機制跟PostgreSQL有什麼不同？

二、Oracle隱式類型轉換機制

上面列出的幾個問題暫時擱置，我們先來看看同樣的SQL在Oracle中的執行結果：

TEST-orcl@DESKTOP-V430TU3>create table t_cast (id int) tablespace users;

Table created.

TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(1);

1 row created.

TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(2);

1 row created.

TEST-orcl@DESKTOP-V430TU3>insert into t_cast values(3);

1 row created.

TEST-orcl@DESKTOP-V430TU3>select * from t_cast where id = '1';

ID

———-

1

TEST-orcl@DESKTOP-V430TU3>select * from t_cast where id = cast('1' as varchar2(2));

ID

———-

1

在Oracle中不會出錯，查閱Oracle文檔[1]，發現Oracle使用數據類型優先順序來判定隱式數據類型轉換的先後順序，優先順序由高到低順序如下：

1. Datetime和interval數據類型

2. BINARY_DOUBLE

3. BINARY_FLOAT

4. NUMBER

5. 字元數據類型

6. 其他內置數據類型

以上例來說，NUMBER與字元數據類型進行等值比較，那麼字元類型會自動隱式轉換為NUMBER進行比較，而不是把NUMBER轉換為字元類型，因為NUMBER優先順序高於字元類型。

關於Oracle的隱式轉換機制，根據Oracle文檔[1]，其轉換規則如下:

1. 在INSERT/UPDATE操作中，轉換數據值為相應的列數據類型

2. 在SELECT FROM操作中，轉換列數據類型為目標類型

3. 在操作數值型數據時，調整為最大可用的精度&刻度。在這種情況下，這些操作產生的數據類型可能與基表中的數據類型不同

4. 在比較字元型和數值型數據時，轉換字元型為數值型

5. 在字元型/數值型數據與浮點數之間轉換時可能不精確，因為字元類型和NUMBER使用十進位精度，而浮點數使用二進位精度

6. 轉換CLOB為字元類型(如VARCHAR2)，或者轉換BLOB轉換為RAW時，如果需要轉換的數據大小比目標數據類型可表示的要大，則返回錯誤

7. TIMESTAMP轉換為DATE時，時間戳中秒的小數部分將被截斷(較老的版本會四捨五入)

8. BINARY_FLOAT轉換為BINARY_DOUBLE時不會丟失精度

9. BINARY_DOUBLE轉換為BINARY_FLOAT會丟失精度(如使用超過float更大的位數來表示精度)

10. 在比較字元型和日期型數據時，轉換字元型為日期型

11. 輸入的參數與函數或者操作符參數不匹配時，轉換為相應的數據類型

12. 在賦值時，把等號右邊的數據轉換為左邊目標數據類型

13. 在字元串拼接操作時，轉換非字元類型為字元類型

14. 在對字元/非字元數據類型進行算術運算/比較時，根據需要會將所有字元類型轉換為數值/日期/Rowid

15. 大多數的SQL函數可接受CLOB類型作為參數，這時候會執行CLOB和字元類型之間的轉換。如果CLOB大小超過4000Byte，則只獲取CLOB中的4000Byte

16. 在RAW/LONG RAW和字元類型之間相互轉換時，二進位數據會被表示為十六進位的格式，一個十六進位字元表示RAW數據中的4位

17. 在CHAR和VARCHAR2以及NCHAR和NVARCHAR2之間比較時可能需要不同的字符集。這種情況下默認的轉換方向是從資料庫字符集轉換為國家字符集

更詳細的資訊請參考Oracle文檔。

三、PostgreSQL隱式類型轉換機制

這一小節通過回答先前提到幾個問題來嘗試解析PostgreSQL的隱式類型轉換機制。

』1』和』1』::text是一回事嗎？

在SQL語句中，』1』和』1』::text是一回事嗎？從實際執行結果來看，顯然不是一回事，否則使用後者就不會出錯了。

一般來說，在PostgreSQL中，SQL語句在執行前需要經過三個步驟：詞法分析、語法分析和語義分析。在詞法分析階段，SQL語句中的』1』會被視為string literal（字元串文字），注意是string literal不是string value（字元串值）！string literal意思是在單引號或雙引號中的一串字元，在PostgreSQL中如果string literal沒有指定類型，那麼該string literal的數據類型會被認為是unknown，如指定類型則為指定的數據類型。例如：

select * from t_cast where id = '1';

未指定類型，『1』類型為unknown

select * from t_cast where id = '1'::text;

指定了數據類型，1』的類型為text

int與text類型比較，為何會報錯？

int與text類型比較，為何會報錯？實際上，PostgreSQL根據系統目錄中的定義來確定類型D1和類型D2能否執行某個操作O。

在示例中，『=』為二元操作符，左操作數類型為int，右操作數類型為text，在pg_operator系統目錄中，不存在該operator和相應操作數類型的定義：

testdb=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprleft::regtype='int'::regtype;

oprname | oprleft | oprright | oprcode

———+———+———-+———

= | integer | bigint | int48eq

= | integer | integer | int4eq

= | integer | smallint | int42eq

(3 rows)

testdb=# select oprname,oprleft::regtype,oprright::regtype,oprcode from pg_operator where oprname='=' and oprright::regtype='text'::regtype;

oprname | oprleft | oprright | oprcode

———+———+———-+————

= | text | text | texteq

= | name | text | nameeqtext

(2 rows)

而且int和text並沒有在pg_cast系統目錄中定義為可相互轉換：

testdb=# select castsource::regtype,casttarget::regtype,castfunc from pg_cast where castsource::regtype='integer'::regtype;

castsource | casttarget | castfunc

————+——————+———-

integer | bigint | 481

integer | smallint | 314

integer | real | 318

integer | double precision | 316

integer | numeric | 1740

integer | money | 3811

integer | boolean | 2557

integer | oid | 0

integer | regproc | 0

integer | regprocedure | 0

integer | regoper | 0

integer | regoperator | 0

integer | regclass | 0

integer | regtype | 0

integer | regconfig | 0

integer | regdictionary | 0

integer | regrole | 0

integer | regnamespace | 0

integer | "char" | 78

integer | bit | 1683

(20 rows)

testdb=# select castsource::regtype,casttarget::regtype,castfunc from pg_cast where castsource::regtype='text'::regtype;

castsource | casttarget | castfunc

————+——————-+———-

text | regclass | 1079

text | character | 0

text | character varying | 0

text | "char" | 944

text | name | 407

text | xml | 2896

(6 rows)

既沒有定義operator也沒有定義數據類型cast，因此int與text等值比較會報錯。

如何確定轉換方向？

接下來的一個問題是int類型與unknown類型以及int與text比較，是int轉換為text類型還是text類型轉換為int類型？是否有優先順序之分？

第一個問題，int類型與unknown類型比較的轉換方向。在PostgreSQL中，不單是int類型，包括其他確定的數據類型，如存在滿足條件的operator，unknown都會轉換為確定的數據類型進行比較，轉換失敗則報錯。

例如：

testdb=# select * from t_cast where id = '1.1';

psql: ERROR: invalid input syntax for type integer: "1.1"

LINE 1: select * from t_cast where id = '1.1';

^

id為int類型，『1.1』為unknown類型，PostgreSQL會嘗試吧『1.1』轉換為整型與id進行比較，轉換不了則失敗報錯。值得一提的是，報錯資訊提示語法錯誤，也就是說PostgreSQL在語法分析階段已發現錯誤而不是在執行階段才發現數據類型轉換錯誤。

第二個問題，int與text比較時的轉換方向。剛才已看到，在原生的PostgreSQL中沒有定義int與text的轉換，但可通過create cast命令自定義類型轉換：

testdb=# create cast(integer as text) with inout as implicit;

CREATE CAST

testdb=# create cast(text as integer) with inout as implicit;

CREATE CAST

我們同時定義了int和text的相互轉換，那到底使用哪一個CAST呢？

testdb=# explain select * from t_cast where id = '1'::text;

psql: ERROR: operator is not unique: integer = text

LINE 1: explain select * from t_cast where id = '1'::text;

^

HINT: Could not choose a best candidate operator. You might need to add explicit type casts.

PostgreSQL給出的答案是無法處理，無法確定使用哪一個候選而報錯。如果我們需要實現Oracle兼容性（轉換為NUMBER比較），那麼保留text到int的轉換即可：

testdb=# drop cast(integer as text);

DROP CAST

testdb=# explain select * from t_cast where id = '1'::text;

QUERY PLAN

——————————————————–

Seq Scan on t_cast (cost=0.00..41.88 rows=13 width=4)

Filter: (id = 1)

(2 rows)

除了定義cast來實現int和text的比較外，還可以通過自定義operator來實現，這一部分不在本文的討論範圍，暫且略過。

PostgreSQL的隱式類型轉換在官方文檔有詳細說明[2]，這裡不再詳細展開。

四、PostgreSQL隱式類型轉換原理

下面從源碼(PostgreSQL 12 beta1)上來對隱式類型轉換的原理作一簡要解析，包括使用的數據結構FormData_pg_cast以及實現函數make_op等。

數據結構

FormData_pg_cast結構體定義了pg_cast中數據的結構

/* —————-

* pg_cast definition. cpp turns this into

* typedef struct FormData_pg_cast

* —————-

*/

CATALOG(pg_cast,2605,CastRelationId)

{

Oid oid; /* oid */

/* source datatype for cast */

Oid castsource BKI_LOOKUP(pg_type);//源類型

/* destination datatype for cast */

Oid casttarget BKI_LOOKUP(pg_type);//目標類型

/* cast function; 0 = binary coercible */

Oid castfunc BKI_LOOKUP(pg_proc);//轉換函數

/* contexts in which cast can be used */

char castcontext;//上下文,i-表達式,a-賦值,e-顯式類型轉換

/* cast method */

//f-通過castfunc定義的函數轉換,b-二元運算符中的轉換,i-函數參數轉換

char castmethod;

} FormData_pg_cast;

/* —————-

* Form_pg_cast corresponds to a pointer to a tuple with

* the format of pg_cast relation.

* —————-

*/

typedef FormData_pg_cast *Form_pg_cast;

make_op

該函數通過變換操作符表達式以及執行類型轉換以確保操作數類型兼容。

相關程式碼如下：

Expr *

make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree,

Node *last_srf, int location)

{

…

/* otherwise, binary operator */

//二元操作符

ltypeId = exprType(ltree);

rtypeId = exprType(rtree);

tup = oper(pstate, opname, ltypeId, rtypeId, false, location);

…

/* otherwise, binary operator */

//二元操作符

args = list_make2(ltree, rtree);

actual_arg_types[0] = ltypeId;

actual_arg_types[1] = rtypeId;

declared_arg_types[0] = opform->oprleft;//左操作數

declared_arg_types[1] = opform->oprright;//右操作數

nargs = 2;

…

//調用函數make_fn_arguments實現參數類型轉換

make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types);

…

make_fn_arguments

調用coerce_type函數實現轉換

void

make_fn_arguments(ParseState *pstate,

List *fargs,

Oid *actual_arg_types,

Oid *declared_arg_types)

{

…

//執行轉換

node = coerce_type(pstate,// ParseState結構體

node,//節點(實際類型為List *)

actual_arg_types[i],//實際操作符

declared_arg_types[i],//聲明操作符

-1,// targetTypeMod

COERCION_IMPLICIT,// CoercionContext

COERCE_IMPLICIT_CAST,// CoercionForm

-1);// location

…

coerce_type

該函數判斷類型是為unknown,如為unknown則轉換為另一確定操作數的數據類型，最終的轉換實現在stringTypeDatum中

Node *

coerce_type(ParseState *pstate, Node *node,

Oid inputTypeId, Oid targetTypeId, int32 targetTypeMod,

CoercionContext ccontext, CoercionForm cformat, int location)

{

…

if (inputTypeId == UNKNOWNOID && IsA(node, Const))

{

//——— 輸入類型為unknown並且是常量

…

Const *con = (Const *) node;//常量

Const *newcon = makeNode(Const);//轉換後的常量

…

/*

* We assume here that UNKNOWN's internal representation is the same

* as CSTRING.

* 內部表示跟CSTRING一樣

*/

if (!con->constisnull)

newcon->constvalue = stringTypeDatum(baseType,

DatumGetCString(con->constvalue),

inputTypeMod);//調用DatumGetCString函數

else

newcon->constvalue = stringTypeDatum(baseType,

NULL,

inputTypeMod);//NULL值

…

stringTypeDatum

stringTypeDatum調用了int4in/ pg_strtoint32函數對string literal進行解析

/*

* int4in – converts "num" to int4

*/

Datum

int4in(PG_FUNCTION_ARGS)

{

char *num = PG_GETARG_CSTRING(0);

PG_RETURN_INT32(pg_strtoint32(num));

}

/*

* Convert input string to a signed 32 bit integer.

*

* Allows any number of leading or trailing whitespace characters. Will throw

* ereport() upon bad input format or overflow.

*

* NB: Accumulate input as a negative number, to deal with two's complement

* representation of the most negative number, which can't be represented as a

* positive number.

*/

int32

pg_strtoint32(const char *s)

{

const char *ptr = s;

int32 tmp = 0;

bool neg = false;

/* skip leading spaces */

while (likely(*ptr) && isspace((unsigned char) *ptr))

ptr++;

/* handle sign */

if (*ptr == '-')

{

ptr++;

neg = true;

}

else if (*ptr == '+')

ptr++;

/* require at least one digit */

if (unlikely(!isdigit((unsigned char) *ptr)))

goto invalid_syntax;

/* process digits */

while (*ptr && isdigit((unsigned char) *ptr))//如'123',-1->-12->-123

{

//獲取數字

int8 digit = (*ptr++ – '0');//』0』到』9』ASCII值 – 『0』得到數字

if (unlikely(pg_mul_s32_overflow(tmp, 10, &tmp)) ||//tmp*10

unlikely(pg_sub_s32_overflow(tmp, digit, &tmp)))//tmp – digit

goto out_of_range;

}

/* allow trailing whitespace, but not other trailing chars */

while (*ptr != '' && isspace((unsigned char) *ptr))

ptr++;

if (unlikely(*ptr != ''))

goto invalid_syntax;

if (!neg)

{

/* could fail if input is most negative number */

if (unlikely(tmp == PG_INT32_MIN))

goto out_of_range;

tmp = -tmp;//取反,-123->123

}

return tmp;

out_of_range://越界

ereport(ERROR,

(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),

errmsg("value "%s" is out of range for type %s",

s, "integer")));

invalid_syntax://非法的語法

ereport(ERROR,

(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),

errmsg("invalid input syntax for type %s: "%s"",

"integer", s)));

return 0; /* keep compiler quiet */

}

PostgreSQL相對於Oracle最大的優勢在於可以通過閱讀源碼了解資料庫的底層實現原理，真正的做到知其然知其所以然。

五、參考資料

1、Oracle Data Type Comparison Rules

https://docs.oracle.com/database/121/SQLRF/sql_elements002.htm#SQLRF30027

2、PostgreSQL Type Conversion

https://www.postgresql.org/docs/11/typeconv-overview.html

VirMach 便宜 VPS

QNews

QNews