标签

PostgreSQL , cast , 数据类型转换 , 自动类型转换 , 隐式转换 , 显示转换 , 整型 , 布尔 , int , boolean

背景

在使用数据库时，经常会遇到一些因为客户端输入的类型与数据库定义的类型不匹配导致的错误问题。

例如数据库定义的是布尔类型，而输入的是整型：

postgres=# create table cas_test(id int, c1 boolean);  CREATE TABLE    postgres=# \set VERBOSITY verbose  postgres=# insert into cas_test values (1, int '1');  ERROR:  42804: column "c1" is of type boolean but expression is of type integer  LINE 1: insert into cas_test values (1, int '1');                                              ^  HINT:  You will need to rewrite or cast the expression.  LOCATION:  transformAssignedExpr, parse_target.c:591

又或者数据库定义的是时间，用户输入的是字符串：

postgres=# create table tbl123(id int, crt_time timestamp);  CREATE TABLE    postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');                                             ^  HINT:  You will need to rewrite or cast the expression.

从错误提示来看，数据库已经很清晰的告诉你为什么了。那么怎么让数据库自动转换呢？

PostgreSQL有一个语法，支持数据类型的转换（赋值、参数、表达式 等位置的自动转换）。

postgres=# \h create cast  Command:     CREATE CAST  Description: define a new cast  Syntax:  CREATE CAST (source_type AS target_type)      WITH FUNCTION function_name [ (argument_type [, ...]) ]      [ AS ASSIGNMENT | AS IMPLICIT ]    CREATE CAST (source_type AS target_type)      WITHOUT FUNCTION      [ AS ASSIGNMENT | AS IMPLICIT ]    CREATE CAST (source_type AS target_type)      WITH INOUT      [ AS ASSIGNMENT | AS IMPLICIT ]

数据库内置了很多转换法则：

postgres=# \dC                                               List of casts           Source type         |         Target type         |         Function          |   Implicit?     -----------------------------+-----------------------------+---------------------------+---------------   abstime                     | date                        | date                      | in assignment   abstime                     | integer                     | (binary coercible)        | no   abstime                     | timestamp without time zone | timestamp                 | yes   ........   integer                     | boolean                     | bool                      | no

类型的自动转换实际上也是有一定的规则的，例如 赋值、参数 算是两种规则。具体含义见如下文档：

我们看到整型转布尔是有内置的转换规则的，那么为什么没有自动转呢？

postgres=# \dC                                               List of casts           Source type         |         Target type         |         Function          |   Implicit?     -----------------------------+-----------------------------+---------------------------+---------------   integer                     | boolean                     | bool                      | no

和自动转换的规则有关，no表示不会自动转换，只有当我们强制指定转换时，才会触发转换的动作：

postgres=# select cast ((int '1') as boolean);   bool   ------   t  (1 row)

pg_cast里面的context转换为可读的内容(e表示no, a表示assignment, 否则表示implicit)

如果让数据库赋值时自动将字符串转换为时间，自动将整型转换为布尔

1、如果数据库已经内置了转换规则，那么可以通过更新系统表的方式，修改自动转换规则。

例如，将这个INT转BOOLEAN的规则，修改为assignment的规则。

postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype;  UPDATE 1

修改后，我们再查看这个转换规则，就变成这样了

\dC                                               List of casts           Source type         |         Target type         |         Function          |   Implicit?     -----------------------------+-----------------------------+---------------------------+---------------   integer                     | boolean                     | bool                      | in assignment

现在你可以将int自动写入为BOOLEAN了。

postgres=# create table cas_test(id int, c1 boolean);  CREATE TABLE  postgres=# insert into cas_test values (1, int '1');  INSERT 0 1

2、如果系统中没有两种类型转换的CAST规则，那么我们需要自定义一个。

例如

postgres=# create cast (text as timestamp) with inout as ASSIGNMENT;  CREATE CAST                                                 List of casts           Source type         |         Target type         |         Function          |   Implicit?     -----------------------------+-----------------------------+---------------------------+---------------   text                        | timestamp without time zone | (binary coercible)        | in assignment

这样就可以自动将TEXT转换为TIMESTAMP了。

postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  INSERT 0 1  postgres=# select * from tbl123;   id |      crt_time         ----+---------------------    1 | 2017-01-01 10:00:00  (1 row)

删掉这个转换，就会报错。

postgres=# drop cast (text as timestamp);  DROP CAST  postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');                                             ^  HINT:  You will need to rewrite or cast the expression.

3、如果没有内置的转换函数，我们可能需要自定义转换函数来支持这种转换。

例子

自定义一个函数，用于输入TEXT，返回TIMESTAMPTZ

postgres=# create or replace function cast_text_to_timestamp(text) returns timestamptz as $$    select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss');  $$ language sql strict ;  CREATE FUNCTION

建立规则

postgres=# create cast (text as timestamptz) with function cast_text_to_timestamp as ASSIGNMENT;  CREATE CAST    postgres=# \dC                                               List of casts           Source type         |         Target type         |         Function          |   Implicit?     -----------------------------+-----------------------------+---------------------------+---------------   text                        | timestamp with time zone    | cast_text_to_timestamp    | in assignment

现在，输入TEXT，就可以自定转换为timestamptz了。

postgres=# create table tbl1234(id int, crt_time timestamptz);  CREATE TABLE  postgres=# insert into tbl1234 values (1, text '2017-01-01 10:10:10');  INSERT 0 1

当然，这些类型实际上内部都有内部的存储格式，大多数时候，如果存储格式通用，就可以直接使用INOUT来转换，不需要写转换函数。

仅仅当两种类型在数据库的内部存储格式不一样的时候，需要显示的写函数来转换。

参考