PostgreSQL与Oracle在数据比较上存在差异,本节简单介绍OPostgreSQL的数据类型转换规则(第三部分). ### 一、Value Storage 插入到数据表的值按如下步骤转换为目标列数据类型. **Value Storage Type Conversion** 1.检查是否与目标完全匹配 2.如不能完全匹配,则转换表达式为目标类型.如果在pg_cast系统目录中存在两种类型的转换信息,那么这是可以做到的.否则,如果表达式为unknown类型,literal string的内容会反馈给输入转换例程,用于转换为目标类型. 3.检查目标类型是否有sizing转换.sizing cast是从该类型到自身的一种转换.如在pg_cast找到相应的cast,在存储到目标列前在该表达式上应用该转换.实现函数通常需要一个额外类为integer的参数,用于接收目标列的atttypmod属性值(该值通常用于表示长度),同时需要第3个类型为boolean的参数用于确定cast是显式还是隐式转换.转换函数有责任执行所有与长度相关的语义如大小检查或截断. 下面是一些例子: *character 字符类型转换* ``` testdb=# CREATE TABLE vv (v character(20)); CREATE TABLE testdb=# INSERT INTO vv SELECT 'abc' || 'def'; INSERT 0 1 testdb=# SELECT v, octet_length(v) FROM vv; v | octet_length ----------------------+-------------- abcdef | 20 (1 row) ``` 'abc'和'def'这两者类型被视为unknown,转换为text类型执行连接操作,||操作的结果为text,该结果转换为bpchar(blank-padded char,character的内部名称)用以匹配目标列类型. 查看pg_cast系统目录 ``` testdb=# select oid,typname from pg_type where typname in ('text','bpchar','char'); oid | typname ------+--------- 18 | char 25 | text 1042 | bpchar (3 rows) testdb=# select * from pg_cast where castsource=25; oid | castsource | casttarget | castfunc | castcontext | castmethod -------+------------+------------+----------+-------------+------------ 11381 | 25 | 2205 | 1079 | i | f 11397 | 25 | 1042 | 0 | i | b 11398 | 25 | 1043 | 0 | i | b 11409 | 25 | 18 | 944 | a | f 11412 | 25 | 19 | 407 | i | f 11466 | 25 | 142 | 2896 | e | f (6 rows) ``` 转换函数 ``` testdb=# select oid,proname,prorettype,proargtypes,prosrc from pg_proc where oid in (0,407,944,1079,2896); oid | proname | prorettype | proargtypes | prosrc ------+----------+------------+-------------+--------------- 407 | name | 19 | 25 | text_name 944 | char | 18 | 25 | text_char 1079 | regclass | 2205 | 25 | text_regclass 2896 | xml | 142 | 25 | texttoxml (4 rows) ``` ### 二、UNION, CASE, and related constructs UNION(INTERSECT/EXCEPT)必须匹配可能的不太相同的类型以成形成一个单独的结果集.该解析算法分别应用于union查询的每一个输出列.CASE, ARRAY, VALUES, GREATEST 和 LEAST使用相同的算法匹配表达式并选择结果数据类型. **Type Resolution for UNION, CASE, and Related Constructs** 1.如果所有输入是同一种类型,而且不是unknown,则解析为该类型 2.如果所有输入为同一个domain类型,则后续步骤的处理的时候把类型视为domain的基类型 3.如果所有输入类型为unknown,则解析为text(字符串的首选类型).否则,unknown类型的输入接下来会被忽略 4.如果非unknown类型不是同一个类型,则失败 5.选择第一个非unknown类型的首选类型 6.否则,选择允许所有优先非unknown输入可被隐私转换的最后一个非unknown输入类型 7.转换所有输入为选择的类型,如存在不能转换的类型,则失败 下面是一些例子: *Simple Union* ``` testdb=# SELECT 1.2 AS "numeric" UNION SELECT 1; numeric --------- 1 1.2 (2 rows) ``` 1.2明确为numeric类型,integer 1可隐式转换为numeric,因此使用numeric类型 *Transposed Union* ``` testdb=# SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL); real ------ 1 2.2 (2 rows) ``` 实数不能隐式转换为整数,但整数可隐式转换为real,因此1会被转换为real,union结果类型为real. *Nested Union* ``` testdb=# SELECT NULL UNION SELECT NULL UNION SELECT 1; psql: ERROR: UNION types text and integer cannot be matched LINE 1: SELECT NULL UNION SELECT NULL UNION SELECT 1; ``` PG会把多个Unions视为成对的操作,该SQL可被视为"(SELECT NULL UNION SELECT NULL) UNION SELECT 1".按照上面的规则,内部union会解析为text类型,外部union的输入为text和integer类型,导致上述错误. ### 三、参考资料 [PostgreSQL Type Conversion](https://www.postgresql.org/docs/11/typeconv-query.html)