Oracle vs PostgreSQL，研发注意事项（9）- PostgreSQL数据类型转换规则#1

postgresql与oracle在数据比较上存在差异,本节简单介绍Postgresql的数据类型转换规则.

一、概述

PostgreSQL比起其他数据库有更强和更灵活的扩展类型系统.先前介绍的PG的词法和语法分析的扫描器/分析器把词法元素拆分为5种类型:integers(整型), non-integer numbers(非整型数字), strings(字符串), identifiers(标识符), 和key Words(关键字),大多数的非数字类型会首先归类为字符串.在SQL中可通过指定类型名称从而让语法分析器”走在”正确的解析路径上.
如:

testdb=# SELECT text 'Origin' AS "label", point '(0,0)' AS "value";
 label  | value 
--------+-------
 Origin | (0,0)
(1 row)

上述SQL有两个字面(literal)常量,类型分别是text和point.对于字符串文字(literal)如果没有指定类型,该占位符(placeholder)的类型会认为是 unknown ,在后续解析阶段在确定具体的类型.
在PG的分析器中,有4中基本的SQL结构要求有唯一的类型转换规则,分别是:
Function calls
PostgreSQL支持函数重载,函数名称并不唯一,因此要求基于提供的参数类型确定相应的函数.
Operators
PostgreSQL允许一元&二元操作符,与函数一样,操作符也支持重载,也需要基于提供的参数类型确定对应的操作符.
Value Storage
INSERT&UPDATE语句中的表达式必须与目标列类型一致或者可转换为目标列类型.
UNION, CASE, and related constructs
UNION类型的结果必须匹配且能转换为统一的集合.CASE和其他相关的结构与此类似.

系统目录存储了数据类型之间如何(强制)转换以及如何执行这些转换的信息,可以通过CREATE CASE自定义强制类型转换.
数据类型划分为几个级别的类型目录(cateGories),包括boolean, numeric, string, bitstring, datetime, timespan, geometric, network, 和 user-defined类型.在每一个目录内部,有1个或多个首选类型,通过仔细选择首选类型和可用的隐式强制转换,可以确保以一种有用的方式处理表达式歧义.
所有的转换规则遵循以下的原则:
1.隐式转换永远不应该产生不可预测的结果
2.不需要隐式转换时不应有额外的分析或执行负载
3.某个查询语句中的函数需要隐式转换,如用户定义了不需要隐式转换的函数,分析器应使用新函数而不应使用旧函数

二、操作符

操作符表达式引用的特定操作符使用以下过程确定,注意该过程间接受相关操作符优先级的影响,因为这会确定那个子表达式将会视为那个操作符的输入.
Operator Type Resolution
1.在pg_operator系统目录中选择哪些操作符.通常情况下,如果操作符不指定schema,则在当前搜索路径中选择名称&参数个数匹配的操作符,否则将选择指定schema的操作符.
a.如在该搜索路径中存在多个相同参数类型的操作符,则选择最早出现的那个.对于具有不同参数类型的操作符,无论搜索路径如何设置,都被认为是平等的.
2.检查操作符是否接受输入参数类型.如存在,则使用此操作符.
a.如果二元操作符中的一个参数为unknown类型,假定该参数与本次检查中的另外一个参数类型相同.在此步骤中,如一/二元操作符的一/两个参数类型都是unknown,那么将找不到匹配的操作符.
b.如果二进制操作符调用的一个参数是unknown,另外一个参数是域类型,那么接下来检查是否有一个操作符在两边都接受域的基本类型.
3.寻找最佳匹配
a.丢弃输入参数类型不匹配以及不能转换(隐式)的操作符.unknown literals假定可转换为任意类型.如只剩下一个候选操作符,则结束,否则继续下一个步骤.
b.如果其中一个输入参数是域类型,把此参数视为后续步骤的域基类型.确保该域与域基类型一致以消除歧义.
c.遍历所有候选项,只保留在输入类型上精确匹配的那些操作符.如无精确匹配,则保留所有候选.如只剩下一个,则使用此操作符,否则下一步骤.
d.遍历所有候选项,并保留那些在大多数位置需要类型转换并接受首选类型的候选项.如无精确匹配,则保留所有候选.如只剩下一个后续,则使用此操作符,否则下一步骤.
e.如所有输入参数类型均为unknown,则使用剩下的候选项检查在这些参数位置上接受这些参数的类型目录.在每一个位置上,如所有候选接受该目录,则选择 string 目录.否则,如果索引剩余候选项接受相同的类型目录,则选择此目录;否则会由于正确的选项不能规约而失败.现在丢弃那些不接受已选类型目录的候选项.更进一步,如果所有候选项接受目录中的首选类型,丢弃接受该参数非首选类型的候选项.如经此检查后无候选项留下,则保留所有的候选项,如只剩下一个,则使用这个,否则继续下一步
f.如既存在unknown和已知参数类型,所有已知类型均为同一个类型,假定 unknown 参数也是这种类型,检查哪个候选项可接受在unknown参数.如存在这么一个候选项,则使用该候选项,否则失败.

几个例子:
阶乘

testdb=# SELECT 40 ! AS "40 factorial";
                   40 factorial                   
--------------------------------------------------
 815915283247897734345611269596115894272000000000
(1 row)

一元运算符”!”在标准目录中定义的参数类型为bigint,扫描器会对输入参数的类型视为integer,因此把参数40转换为bigint.

字符串拼接

testdb=# SELECT text 'abc' || 'def' AS "text and unknown";
 text and unknown 
------------------
 abcdef
(1 row)

一边是text,一边是unknown,则假定第二个参数’def’类型为text.

testdb=# SELECT 'abc' || 'def' AS "unspecified";
 unspecified 
-------------
 abcdef
(1 row)

在这种情况下,两边都是unknown,解析器查找所有候选操作符,发现候选操作符同时接受(bit-)string-category输入,由于字符串是首选的,则选择该类别,然后把该类比首选类型text作为特定类型来解析unknown类型的参数.

取绝对值和位取反运算

testdb=# SELECT @ '-4.5' AS "abs";
 abs 
-----
 4.5
(1 row)

取绝对值运算可接受多个输入类型,在数值category中float8是首选类型,因此PG会使用此条目来处理unknown参数类型.
在应用选中的操作符前,系统会隐式处理unknown类型字面量为float8,因此系统会验证输入是否为float8类型,不符合的则报错.

testdb=# SELECT @ '-4.5e500' AS "abs";
psql: ERROR:  "-4.5e500" is out of range for type double precision
LINE 1: SELECT @ '-4.5e500' AS "abs";
                 ^

另一方面,取反运算符~只接受整型类型作为参数,如参数类型为unknown则被视为float8,执行会出错:

testdb=# SELECT ~ '20' AS "negation";
psql: ERROR:  operator is not unique: ~ unknown
LINE 1: SELECT ~ '20' AS "negation";
               ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Function/Value Storage/UNION, CASE, and related constructs下一节再行介绍

三、参考资料

PostgreSQL Type Conversion