PostgreSQL 自定义自动类型转换(CAST)

5 minute read






postgres=# select '1' + '1';  
ERROR:  operator is not unique: unknown + unknown  
LINE 1: select '1' + '1';  
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.  




postgres=# \dC+  
                                                List of casts  
         Source type         |         Target type         |      Function      |   Implicit?   | Description   
 "char"                      | character                   | bpchar             | in assignment |   
 "char"                      | character varying           | text               | in assignment |   
 "char"                      | integer                     | int4               | no            |   
 "char"                      | text                        | text               | yes           |   
 abstime                     | date                        | date               | in assignment |   
 abstime                     | integer                     | (binary coercible) | no            |   
 abstime                     | time without time zone      | time               | in assignment |   
 timestamp without time zone | timestamp with time zone    | timestamptz        | yes           |   
 timestamp without time zone | timestamp without time zone | timestamp          | yes           |   
 xml                         | character                   | (binary coercible) | in assignment |   
 xml                         | character varying           | (binary coercible) | in assignment |   
 xml                         | text                        | (binary coercible) | in assignment |   
(246 rows)  

注意Implicit列,实际上是pg_cast里面的context转换为可读的内容(e表示no, a表示assignment, 否则表示implicit)。

SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"
FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
ORDER BY 1, 2;



CAST(x AS typename)   



CREATE CAST (source_type AS target_type)  
    WITH FUNCTION function_name [ (argument_type [, ...]) ]  
CREATE CAST (source_type AS target_type)  
CREATE CAST (source_type AS target_type)  


1、WITH FUNCTION,表示转换需要用到什么函数。

2、WITHOUT FUNCTION,表示被转换的两个类型,在数据库的存储中一致,即物理存储一致。例如text和varchar的物理存储一致。不需要转换函数。

Two types can be binary coercible,   
which means that the conversion can be performed “for free” without invoking any function.   
This requires that corresponding values use the same internal representation.   
For instance, the types text and varchar are binary coercible both ways.   
Binary coercibility is not necessarily a symmetric relationship.   
For example, the cast from xml to text can be performed for free in the present implementation,   
but the reverse direction requires a function that performs at least a syntax check.   
(Two types that are binary coercible both ways are also referred to as binary compatible.)  

3、WITH INOUT,表示使用内置的IO函数进行转换。每一种类型,都有INPUT 和OUTPUT函数。使用这种方法,好处是不需要重新写转换函数。


                               List of functions  
   Schema   |      Name       | Result data type | Argument data types |  Type    
 pg_catalog | textin          | text             | cstring             | normal  
 pg_catalog | textout         | cstring          | text                | normal  
 pg_catalog | date_in         | date             | cstring             | normal  
 pg_catalog | date_out        | cstring          | date                | normal  
You can define a cast as an I/O conversion cast by using the WITH INOUT syntax.   
An I/O conversion cast is performed by invoking the output function of the source data type,   
and passing the resulting string to the input function of the target data type.   
In many common cases, this feature avoids the need to write a separate cast function for conversion.   
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。

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.   
For example, supposing that foo.f1 is a column of type text, then:  
INSERT INTO foo (f1) VALUES (42);  
will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT,   
otherwise not.   
(We generally use the term assignment cast to describe this kind of cast.)  

5、AS IMPLICIT,表示在表达式中,或者在赋值操作中,都对类型进行自动转换。(包含了AS ASSIGNMENT,它只对赋值进行转换)

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context,   
whether assignment or internally in an expression.   
(We generally use the term implicit cast to describe this kind of cast.)   
For example, consider this query:  
SELECT 2 + 4.0;  
The parser initially marks the constants as being of type integer and numeric respectively.   
There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator.   
The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT —   
which in fact it is.   
The parser will apply the implicit cast and resolve the query as if it had been written  
SELECT CAST ( 2 AS numeric ) + 4.0;  

6、注意,AS IMPLICIT需要谨慎使用,为什么呢?因为操作符会涉及到多个算子,如果有多个转换,目前数据库并不知道应该选择哪个?

Now, the catalogs also provide a cast from numeric to integer.   
If that cast were marked AS IMPLICIT — (which it is not — )  
then the parser would be faced with choosing between the above interpretation and   
the alternative of casting the numeric constant to integer and applying the integer + integer operator.   
Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous.   
The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of   
a mixed numeric-and-integer expression as numeric;   
there is no built-in knowledge about that.  

因此,建议谨慎使用AS IMPLICIT。建议使用AS IMPLICIT的CAST应该是非失真转换转换,例如从INT转换为TEXT,或者int转换为numeric。

而失真转换,不建议使用as implicit,例如numeric转换为int。

It is wise to be conservative about marking casts as implicit.   
An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands,   
or to be unable to resolve commands at all because there are multiple possible interpretations.   
A good rule of thumb is to make a cast implicitly invokable only for information-preserving   
transformations between types in the same general type category.   
For example, the cast from int2 to int4 can reasonably be implicit,   
but the cast from float8 to int4 should probably be assignment-only.   
Cross-type-category casts, such as text to int4, are best made explicit-only.  

注意事项 + 例子




create or replace function text_to_date(text) returns date as $$  
  select cast($1 as date);  
$$ language sql strict;  
create cast (text as date) with function text_to_date(text) as implicit;  


postgres=# select text '2017-01-01' + 1;  
ERROR:  stack depth limit exceeded  
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.  
CONTEXT:  SQL function "text_to_date" during startup  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  


create or replace function text_to_date(text) returns date as $$          
  select to_date($1,'yyyy-mm-dd');  
$$ language sql strict;  
create cast (text as date) with function text_to_date(text) as implicit;  
postgres=# select text '2017-01-01' + 1;  
(1 row)  


postgres=# create cast (text as date) with inout as implicit;

postgres=# select text '2017-01-01' + 1;
(1 row)


《PostgreSQL 整型int与布尔boolean的自动转换设置》

Flag Counter

digoal’s 大量PostgreSQL文章入口