个人简介

何小栋， 从事产品研发和架构设计工作，对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

该函数通过变换操作符表达式以及执行类型转换以确保操作数类型兼容。

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 != '\0' && isspace((unsigned char) *ptr))

ptr++;

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

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

ITPub博客

PostgreSQL 隐式类型转换探秘

摘要

一、缘起

二、Oracle隐式类型转换机制

三、PostgreSQL隐式类型转换机制

’1’和’1’::text是一回事吗？

int与text类型比较，为何会报错？

如何确定转换方向？

四、PostgreSQL隐式类型转换原理

数据结构

make_op

make_fn_arguments

coerce_type

stringTypeDatum

五、参考资料

最新文章