• 售前

  • 售后

热门帖子
入门百科

PostgreSQL 自界说主动类型转换操作(CAST)

[复制链接]
123457814 显示全部楼层 发表于 2021-10-26 12:45:55 |阅读模式 打印 上一主题 下一主题
配景

PostgreSQL是一个强类型数据库,因此你输入的变量、常量是什么类型,是强绑定的,例如
在调用利用符时,须要通过利用符边上的数据类型,选择对应的利用符。
在调用函数时,须要根据输入的类型,选择对应的函数。
假如类型不匹配,就会报利用符不存在,大概函数不存在的错误。
  1. postgres=# select '1' + '1';
  2. ERROR: operator is not unique: unknown + unknown
  3. LINE 1: select '1' + '1';
  4.      ^
  5. HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
复制代码
那么使用起来是不是很不方便呢?
PostgreSQL开放了类型转换的接口,同时也内置了许多的自动类型转换。来简化利用。
检察如今已有的类型转换:
  1. postgres=# \dC+
  2.             List of casts
  3.    Source type   |   Target type   |  Function  | Implicit? | Description
  4. -----------------------------+-----------------------------+--------------------+---------------+-------------
  5. "char"      | character     | bpchar    | in assignment |
  6. "char"      | character varying   | text    | in assignment |
  7. "char"      | integer      | int4    | no   |
  8. "char"      | text      | text    | yes   |
  9. abstime      | date      | date    | in assignment |
  10. abstime      | integer      | (binary coercible) | no   |
  11. abstime      | time without time zone  | time    | in assignment |
  12. ................................
  13. timestamp without time zone | timestamp with time zone | timestamptz  | yes   |
  14. timestamp without time zone | timestamp without time zone | timestamp   | yes   |
  15. xml       | character     | (binary coercible) | in assignment |
  16. xml       | character varying   | (binary coercible) | in assignment |
  17. xml       | text      | (binary coercible) | in assignment |
  18. (246 rows)
复制代码
假如你发现有些类型转换没有内置,怎么办呢?我们可以自界说转换。
当然你也可以使用这种语法,对类型举行欺压转换:
  1. CAST(x AS typename)
  2. or
  3. x::typename
复制代码
如何自界说类型转换(CAST)

自界说CAST的语法如下:
  1. CREATE CAST (source_type AS target_type)
  2. WITH FUNCTION function_name [ (argument_type [, ...]) ]
  3. [ AS ASSIGNMENT | AS IMPLICIT ]
  4. CREATE CAST (source_type AS target_type)
  5. WITHOUT FUNCTION
  6. [ AS ASSIGNMENT | AS IMPLICIT ]
  7. CREATE CAST (source_type AS target_type)
  8. WITH INOUT
  9. [ AS ASSIGNMENT | AS IMPLICIT ]
复制代码
解释:
1、WITH FUNCTION,体现转换须要用到什么函数。
2、WITHOUT FUNCTION,体现被转换的两个类型,在数据库的存储中划一,即物理存储划一。例如text和varchar的物理存储划一。不须要转换函数。
  1. Two types can be binary coercible,
  2. which means that the conversion can be performed “for free” without invoking any function.
  3. This requires that corresponding values use the same internal representation.
  4. For instance, the types text and varchar are binary coercible both ways.
  5. Binary coercibility is not necessarily a symmetric relationship.
  6. For example, the cast from xml to text can be performed for free in the present implementation,
  7. but the reverse direction requires a function that performs at least a syntax check.
  8. (Two types that are binary coercible both ways are also referred to as binary compatible.)
复制代码
3、WITH INOUT,体现使用内置的IO函数举行转换。每一种类型,都有INPUT 和OUTPUT函数。使用这种方法,利益是不须要重新写转换函数。
除非有特殊需求,我们发起直接使用IO函数来举行转换。
  1.         List of functions
  2. Schema |  Name  | Result data type | Argument data types | Type
  3. ------------+-----------------+------------------+---------------------+--------
  4. pg_catalog | textin   | text    | cstring    | normal
  5. pg_catalog | textout   | cstring   | text    | normal
  6. pg_catalog | date_in   | date    | cstring    | normal
  7. pg_catalog | date_out  | cstring   | date    | normal
  8. You can define a cast as an I/O conversion cast by using the WITH INOUT syntax.
  9. An I/O conversion cast is performed by invoking the output function of the source data type,
  10. and passing the resulting string to the input function of the target data type.
  11. In many common cases, this feature avoids the need to write a separate cast function for conversion.
  12. An I/O conversion cast acts the same as a regular function-based cast; only the implementation is different.
复制代码
4、AS ASSIGNMENT,体现在赋值时,自动对类型举行转换。例如字段类型为TEXT,输入的类型为INT,那么可以创建一个 cast(int as text) as ASSIGNMENT。
  1. If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type.
  2. For example, supposing that foo.f1 is a column of type text, then:
  3. INSERT INTO foo (f1) VALUES (42);
  4. will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT,
  5. otherwise not.
  6. (We generally use the term assignment cast to describe this kind of cast.)
复制代码
5、AS IMPLICIT,体现在表达式中,大概在赋值利用中,都对类型举行自动转换。(包罗了AS ASSIGNMENT,它只对赋值举行转换)
  1. If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context,
  2. whether assignment or internally in an expression.
  3. (We generally use the term implicit cast to describe this kind of cast.)
  4. For example, consider this query:
  5. SELECT 2 + 4.0;
  6. The parser initially marks the constants as being of type integer and numeric respectively.
  7. There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator.
  8. The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT —
  9. which in fact it is.
  10. The parser will apply the implicit cast and resolve the query as if it had been written
  11. SELECT CAST ( 2 AS numeric ) + 4.0;
复制代码
6、注意,AS IMPLICIT须要谨慎使用,为什么呢?因为利用符会涉及到多个算子,假如有多个转换,如今数据库并不知道应该选择哪个?
  1. Now, the catalogs also provide a cast from numeric to integer.
  2. If that cast were marked AS IMPLICIT — (which it is not — )
  3. then the parser would be faced with choosing between the above interpretation and
  4. the alternative of casting the numeric constant to integer and applying the integer + integer operator.
  5. Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous.
  6. The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of
  7. a mixed numeric-and-integer expression as numeric;
  8. there is no built-in knowledge about that.
复制代码
因此,发起谨慎使用AS IMPLICIT。发起使用AS IMPLICIT的CAST应该好坏失真转换转换,例如从INT转换为TEXT,大概int转换为numeric。
而失真转换,不发起使用as implicit,例如numeric转换为int。
  1. It is wise to be conservative about marking casts as implicit.
  2. An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands,
  3. or to be unable to resolve commands at all because there are multiple possible interpretations.
  4. A good rule of thumb is to make a cast implicitly invokable only for information-preserving
  5. transformations between types in the same general type category.
  6. For example, the cast from int2 to int4 can reasonably be implicit,
  7. but the cast from float8 to int4 should probably be assignment-only.
  8. Cross-type-category casts, such as text to int4, are best made explicit-only.
复制代码
注意事项 + 例子

不能嵌套转换。例子
1、将text转换为date
错误方法
  1. create or replace function text_to_date(text) returns date as $$
  2. select cast($1 as date);
  3. $$ language sql strict;
  4. create cast (text as date) with function text_to_date(text) as implicit;
复制代码
嵌套转换后出现死循环
  1. postgres=# select text '2017-01-01' + 1;
  2. ERROR: stack depth limit exceeded
  3. HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
  4. CONTEXT: SQL function "text_to_date" during startup
  5. SQL function "text_to_date" statement 1
  6. SQL function "text_to_date" statement 1
  7. SQL function "text_to_date" statement 1
  8. ......
复制代码
准确方法
  1. create or replace function text_to_date(text) returns date as $$   
  2. select to_date($1,'yyyy-mm-dd');
  3. $$ language sql strict;
  4. create cast (text as date) with function text_to_date(text) as implicit;
  5. postgres=# select text '2017-01-01' + 1;
  6. ?column?
  7. ------------
  8. 2017-01-02
  9. (1 row)
复制代码
我们还可以直接使用IO函数来转换:
  1. postgres=# create cast (text as date) with inout as implicit;
  2. CREATE CAST
  3. postgres=# select text '2017-01-01' + 1;
  4. ?column?
  5. ------------
  6. 2017-01-02
  7. (1 row)
复制代码
增补:PostgreSQL 整型int与布尔boolean的自动转换设置(含自界说cast与cast规则介绍)
配景
在使用数据库时,常常会遇到一些因为客户端输入的类型与数据库界说的类型不匹配导致的错误问题。
例如数据库界说的是布尔类型,而输入的是整型:
  1. postgres=# create table cas_test(id int, c1 boolean);
  2. CREATE TABLE
  3. postgres=# \set VERBOSITY verbose
  4. postgres=# insert into cas_test values (1, int '1');
  5. ERROR: 42804: column "c1" is of type boolean but expression is of type integer
  6. LINE 1: insert into cas_test values (1, int '1');
  7.            ^
  8. HINT: You will need to rewrite or cast the expression.
  9. LOCATION: transformAssignedExpr, parse_target.c:591
复制代码
又大概数据库界说的是时间,用户输入的是字符串:
  1. postgres=# create table tbl123(id int, crt_time timestamp);
  2. CREATE TABLE
  3. postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');
  4. ERROR: column "crt_time" is of type timestamp without time zone but expression is of type text
  5. LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');
  6.            ^
  7. HINT: You will need to rewrite or cast the expression.
复制代码
从错误提示来看,数据库已经很清楚的告诉你为什么了。那么怎么让数据库自动转换呢?
PostgreSQL有一个语法,支持数据类型的转换(赋值、参数、表达式 等位置的自动转换)。
  1. postgres=# \h create cast Command:  CREATE CAST Description: define a new cast Syntax: CREATE CAST (source_type AS target_type)
  2. WITH FUNCTION function_name [ (argument_type [, ...]) ]
  3. [ AS ASSIGNMENT | AS IMPLICIT ]
  4. CREATE CAST (source_type AS target_type)
  5. WITHOUT FUNCTION
  6. [ AS ASSIGNMENT | AS IMPLICIT ]
  7. CREATE CAST (source_type AS target_type)
  8. WITH INOUT
  9. [ AS ASSIGNMENT | AS IMPLICIT ]
复制代码
数据库内置了许多转换法则:
  1. postgres=# \dC
  2.             List of casts
  3.    Source type   |   Target type   |   Function   | Implicit?  
  4. -----------------------------+-----------------------------+---------------------------+---------------
  5. abstime      | date      | date      | in assignment
  6. abstime      | integer      | (binary coercible)  | no
  7. abstime      | timestamp without time zone | timestamp     | yes
  8. ........
  9. integer      | boolean      | bool      | no
复制代码
类型的自动转换现实上也是有一定的规则的,例如 赋值、参数 算是两种规则。具体含义见如下文档:
《PostgreSQL 自界说自动类型转换(CAST)》
我们看到整型转布尔是有内置的转换规则的,那么为什么没有自动转呢?
  1. postgres=# \dC
  2.             List of casts
  3.    Source type   |   Target type   |   Function   | Implicit?  
  4. -----------------------------+-----------------------------+---------------------------+---------------
  5. integer      | boolean      | bool      | no
复制代码
和自动转换的规则有关,no体现不会自动转换,只有当我们欺压指定转换时,才会触发转换的动作:
  1. postgres=# select cast ((int '1') as boolean);
  2. bool
  3. ------
  4. t
  5. (1 row)
复制代码
pg_cast内里的context转换为可读的内容(e体现no, a体现assignment, 否则体现implicit)
假如让数据库赋值时自动将字符串转换为时间,自动将整型转换为布尔
1、假如数据库已经内置了转换规则,那么可以通过更新系统表的方式,修改自动转换规则。
例如,将这个INT转BOOLEAN的规则,修改为assignment的规则。
  1. postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype;
  2. UPDATE 1
复制代码
修改后,我们再检察这个转换规则,就变成这样了
  1. \dC
  2.             List of casts
  3.    Source type   |   Target type   |   Function   | Implicit?  
  4. -----------------------------+-----------------------------+---------------------------+---------------
  5. integer      | boolean      | bool      | in assignment
复制代码
如今你可以将int自动写入为BOOLEAN了。
  1. postgres=# create table cas_test(id int, c1 boolean);
  2. CREATE TABLE
  3. postgres=# insert into cas_test values (1, int '1');
  4. INSERT 0 1
复制代码
2、假如系统中没有两种类型转换的CAST规则,那么我们须要自界说一个。
例如
  1. postgres=# create cast (text as timestamp) with inout as ASSIGNMENT;
  2. CREATE CAST
  3.             List of casts
  4.    Source type   |   Target type   |   Function   | Implicit?  
  5. -----------------------------+-----------------------------+---------------------------+---------------
  6. text      | timestamp without time zone | (binary coercible)  | in assignment
复制代码
这样就可以自动将TEXT转换为TIMESTAMP了。
  1. postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');
  2. INSERT 0 1
  3. postgres=# select * from tbl123;
  4. id |  crt_time   
  5. ----+---------------------
  6. 1 | 2017-01-01 10:00:00
  7. (1 row)
复制代码
删掉这个转换,就会报错。
  1. postgres=# drop cast (text as timestamp);
  2. DROP CAST
  3. postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');
  4. ERROR: column "crt_time" is of type timestamp without time zone but expression is of type text
  5. LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');
  6.            ^
  7. HINT: You will need to rewrite or cast the expression.
复制代码
3、假如没有内置的转换函数,我们大概须要自界说转换函数来支持这种转换。
例子
自界说一个函数,用于输入TEXT,返回TIMESTAMPTZ
  1. postgres=# create or replace function cast_text_to_timestamp(text) returns timestamptz as $$
  2. select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss');
  3. $$ language sql strict ;
  4. CREATE FUNCTION
复制代码
创建规则
  1. postgres=# create cast (text as timestamptz) with function cast_text_to_timestamp as ASSIGNMENT;
  2. CREATE CAST
  3. postgres=# \dC
  4.             List of casts
  5.    Source type   |   Target type   |   Function   | Implicit?  
  6. -----------------------------+-----------------------------+---------------------------+---------------
  7. text      | timestamp with time zone | cast_text_to_timestamp | in assignment
复制代码
如今,输入TEXT,就可以自定转换为timestamptz了。
  1. postgres=# create table tbl1234(id int, crt_time timestamptz);
  2. CREATE TABLE
  3. postgres=# insert into tbl1234 values (1, text '2017-01-01 10:10:10');
  4. INSERT 0 1
复制代码
当然,这些类型现实上内部都有内部的存储格式,大多数时候,假如存储格式通用,就可以直接使用INOUT来转换,不须要写转换函数。
仅仅当两种类型在数据库的内部存储格式不一样的时候,须要显示的写函数来转换。
以上为个人履历,渴望能给大家一个参考,也渴望大家多多支持脚本之家。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作