PostgreSQL 自定义自动类型转换操作(CAST)
背景
PostgreSQL是一个强类型数据库，因此你输入的变量、常量是什么类型，是强绑定的，例如
在调用操作符时，需要通过操作符边上的数据类型，选择对应的操作符。
在调用函数时，需要根据输入的类型，选择对应的函数。
如果类型不匹配，就会报操作符不存在，或者函数不存在的错误。
postgres=#select'1'+'1'; ERROR:operatorisnotunique:unknown+unknown LINE1:select'1'+'1'; ^ HINT:Couldnotchooseabestcandidateoperator.Youmightneedtoaddexplicittypecasts.
那么使用起来是不是很不方便呢？
PostgreSQL开放了类型转换的接口，同时也内置了很多的自动类型转换。来简化操作。
查看目前已有的类型转换：
postgres=#\dC+ Listofcasts Sourcetype|Targettype|Function|Implicit?|Description -----------------------------+-----------------------------+--------------------+---------------+------------- "char"|character|bpchar|inassignment| "char"|charactervarying|text|inassignment| "char"|integer|int4|no| "char"|text|text|yes| abstime|date|date|inassignment| abstime|integer|(binarycoercible)|no| abstime|timewithouttimezone|time|inassignment| ................................ timestampwithouttimezone|timestampwithtimezone|timestamptz|yes| timestampwithouttimezone|timestampwithouttimezone|timestamp|yes| xml|character|(binarycoercible)|inassignment| xml|charactervarying|(binarycoercible)|inassignment| xml|text|(binarycoercible)|inassignment| (246rows)
如果你发现有些类型转换没有内置，怎么办呢？我们可以自定义转换。
当然你也可以使用这种语法，对类型进行强制转换：
CAST(xAStypename) or x::typename
如何自定义类型转换(CAST)
自定义CAST的语法如下：
CREATECAST(source_typeAStarget_type) WITHFUNCTIONfunction_name[(argument_type[,...])] [ASASSIGNMENT|ASIMPLICIT] CREATECAST(source_typeAStarget_type) WITHOUTFUNCTION [ASASSIGNMENT|ASIMPLICIT] CREATECAST(source_typeAStarget_type) WITHINOUT [ASASSIGNMENT|ASIMPLICIT]
解释：
1、WITHFUNCTION，表示转换需要用到什么函数。
2、WITHOUTFUNCTION，表示被转换的两个类型，在数据库的存储中一致，即物理存储一致。例如text和varchar的物理存储一致。不需要转换函数。
Twotypescanbebinarycoercible, whichmeansthattheconversioncanbeperformed“forfree”withoutinvokinganyfunction. Thisrequiresthatcorrespondingvaluesusethesameinternalrepresentation. Forinstance,thetypestextandvarchararebinarycoerciblebothways. Binarycoercibilityisnotnecessarilyasymmetricrelationship. Forexample,thecastfromxmltotextcanbeperformedforfreeinthepresentimplementation, butthereversedirectionrequiresafunctionthatperformsatleastasyntaxcheck. (Twotypesthatarebinarycoerciblebothwaysarealsoreferredtoasbinarycompatible.)
3、WITHINOUT，表示使用内置的IO函数进行转换。每一种类型，都有INPUT和OUTPUT函数。使用这种方法，好处是不需要重新写转换函数。
除非有特殊需求，我们建议直接使用IO函数来进行转换。
Listoffunctions Schema|Name|Resultdatatype|Argumentdatatypes|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 YoucandefineacastasanI/OconversioncastbyusingtheWITHINOUTsyntax. AnI/Oconversioncastisperformedbyinvokingtheoutputfunctionofthesourcedatatype, andpassingtheresultingstringtotheinputfunctionofthetargetdatatype. Inmanycommoncases,thisfeatureavoidstheneedtowriteaseparatecastfunctionforconversion. AnI/Oconversioncastactsthesameasaregularfunction-basedcast;onlytheimplementationisdifferent.
4、ASASSIGNMENT，表示在赋值时，自动对类型进行转换。例如字段类型为TEXT，输入的类型为INT，那么可以创建一个cast(intastext)asASSIGNMENT。
IfthecastismarkedASASSIGNMENTthenitcanbeinvokedimplicitlywhenassigningavaluetoacolumnofthetargetdatatype. Forexample,supposingthatfoo.f1isacolumnoftypetext,then: INSERTINTOfoo(f1)VALUES(42); willbeallowedifthecastfromtypeintegertotypetextismarkedASASSIGNMENT, otherwisenot. (Wegenerallyusethetermassignmentcasttodescribethiskindofcast.)
5、ASIMPLICIT，表示在表达式中，或者在赋值操作中，都对类型进行自动转换。（包含了ASASSIGNMENT，它只对赋值进行转换）
IfthecastismarkedASIMPLICITthenitcanbeinvokedimplicitlyinanycontext, whetherassignmentorinternallyinanexpression. (Wegenerallyusethetermimplicitcasttodescribethiskindofcast.) Forexample,considerthisquery: SELECT2+4.0; Theparserinitiallymarkstheconstantsasbeingoftypeintegerandnumericrespectively. Thereisnointeger+numericoperatorinthesystemcatalogs,butthereisanumeric+numericoperator. ThequerywillthereforesucceedifacastfromintegertonumericisavailableandismarkedASIMPLICIT— whichinfactitis. Theparserwillapplytheimplicitcastandresolvethequeryasifithadbeenwritten SELECTCAST(2ASnumeric)+4.0;
6、注意，ASIMPLICIT需要谨慎使用，为什么呢？因为操作符会涉及到多个算子，如果有多个转换，目前数据库并不知道应该选择哪个？
Now,thecatalogsalsoprovideacastfromnumerictointeger. IfthatcastweremarkedASIMPLICIT—(whichitisnot—) thentheparserwouldbefacedwithchoosingbetweentheaboveinterpretationand thealternativeofcastingthenumericconstanttointegerandapplyingtheinteger+integeroperator. Lackinganyknowledgeofwhichchoicetoprefer,itwouldgiveupanddeclarethequeryambiguous. Thefactthatonlyoneofthetwocastsisimplicitisthewayinwhichweteachtheparsertopreferresolutionof amixednumeric-and-integerexpressionasnumeric; thereisnobuilt-inknowledgeaboutthat.
因此，建议谨慎使用ASIMPLICIT。建议使用ASIMPLICIT的CAST应该是非失真转换转换，例如从INT转换为TEXT，或者int转换为numeric。
而失真转换，不建议使用asimplicit，例如numeric转换为int。
Itiswisetobeconservativeaboutmarkingcastsasimplicit. AnoverabundanceofimplicitcastingpathscancausePostgreSQLtochoosesurprisinginterpretationsofcommands, ortobeunabletoresolvecommandsatallbecausetherearemultiplepossibleinterpretations. Agoodruleofthumbistomakeacastimplicitlyinvokableonlyforinformation-preserving transformationsbetweentypesinthesamegeneraltypecategory. Forexample,thecastfromint2toint4canreasonablybeimplicit, butthecastfromfloat8toint4shouldprobablybeassignment-only. Cross-type-categorycasts,suchastexttoint4,arebestmadeexplicit-only.
注意事项+例子
不能嵌套转换。例子
1、将text转换为date
错误方法
createorreplacefunctiontext_to_date(text)returnsdateas$$ selectcast($1asdate); $$languagesqlstrict; createcast(textasdate)withfunctiontext_to_date(text)asimplicit;
嵌套转换后出现死循环
postgres=#selecttext'2017-01-01'+1; ERROR:stackdepthlimitexceeded HINT:Increasetheconfigurationparameter"max_stack_depth"(currently2048kB),afterensuringtheplatform'sstackdepthlimitisadequate. CONTEXT:SQLfunction"text_to_date"duringstartup SQLfunction"text_to_date"statement1 SQLfunction"text_to_date"statement1 SQLfunction"text_to_date"statement1 ......
正确方法
createorreplacefunctiontext_to_date(text)returnsdateas$$ selectto_date($1,'yyyy-mm-dd'); $$languagesqlstrict; createcast(textasdate)withfunctiontext_to_date(text)asimplicit; postgres=#selecttext'2017-01-01'+1; ?column? ------------ 2017-01-02 (1row)
我们还可以直接使用IO函数来转换：
postgres=#createcast(textasdate)withinoutasimplicit; CREATECAST postgres=#selecttext'2017-01-01'+1; ?column? ------------ 2017-01-02 (1row)
补充：PostgreSQL整型int与布尔boolean的自动转换设置(含自定义cast与cast规则介绍)
背景
在使用数据库时，经常会遇到一些因为客户端输入的类型与数据库定义的类型不匹配导致的错误问题。
例如数据库定义的是布尔类型，而输入的是整型：
postgres=#createtablecas_test(idint,c1boolean); CREATETABLE postgres=#\setVERBOSITYverbose postgres=#insertintocas_testvalues(1,int'1'); ERROR:42804:column"c1"isoftypebooleanbutexpressionisoftypeinteger LINE1:insertintocas_testvalues(1,int'1'); ^ HINT:Youwillneedtorewriteorcasttheexpression. LOCATION:transformAssignedExpr,parse_target.c:591
又或者数据库定义的是时间，用户输入的是字符串：
postgres=#createtabletbl123(idint,crt_timetimestamp); CREATETABLE postgres=#insertintotbl123values(1,text'2017-01-0110:00:00'); ERROR:column"crt_time"isoftypetimestampwithouttimezonebutexpressionisoftypetext LINE1:insertintotbl123values(1,text'2017-01-0110:00:00'); ^ HINT:Youwillneedtorewriteorcasttheexpression.
从错误提示来看，数据库已经很清晰的告诉你为什么了。那么怎么让数据库自动转换呢？
PostgreSQL有一个语法，支持数据类型的转换（赋值、参数、表达式等位置的自动转换）。
postgres=#\hcreatecast Command:CREATECAST Description:defineanewcast Syntax: CREATECAST(source_typeAStarget_type) WITHFUNCTIONfunction_name[(argument_type[,...])] [ASASSIGNMENT|ASIMPLICIT] CREATECAST(source_typeAStarget_type) WITHOUTFUNCTION [ASASSIGNMENT|ASIMPLICIT] CREATECAST(source_typeAStarget_type) WITHINOUT [ASASSIGNMENT|ASIMPLICIT]
数据库内置了很多转换法则：
postgres=#\dC Listofcasts Sourcetype|Targettype|Function|Implicit? -----------------------------+-----------------------------+---------------------------+--------------- abstime|date|date|inassignment abstime|integer|(binarycoercible)|no abstime|timestampwithouttimezone|timestamp|yes ........ integer|boolean|bool|no
类型的自动转换实际上也是有一定的规则的，例如赋值、参数算是两种规则。具体含义见如下文档：
《PostgreSQL自定义自动类型转换(CAST)》
我们看到整型转布尔是有内置的转换规则的，那么为什么没有自动转呢？
postgres=#\dC Listofcasts Sourcetype|Targettype|Function|Implicit? -----------------------------+-----------------------------+---------------------------+--------------- integer|boolean|bool|no
和自动转换的规则有关，no表示不会自动转换，只有当我们强制指定转换时，才会触发转换的动作：
postgres=#selectcast((int'1')asboolean); bool ------ t (1row)
pg_cast里面的context转换为可读的内容(e表示no,a表示assignment,否则表示implicit)
如果让数据库赋值时自动将字符串转换为时间，自动将整型转换为布尔
1、如果数据库已经内置了转换规则，那么可以通过更新系统表的方式，修改自动转换规则。
例如，将这个INT转BOOLEAN的规则，修改为assignment的规则。
postgres=#updatepg_castsetcastcontext='a'wherecastsource='integer'::regtypeandcasttarget='boolean'::regtype; UPDATE1
修改后，我们再查看这个转换规则，就变成这样了
\dC Listofcasts Sourcetype|Targettype|Function|Implicit? -----------------------------+-----------------------------+---------------------------+--------------- integer|boolean|bool|inassignment
现在你可以将int自动写入为BOOLEAN了。
postgres=#createtablecas_test(idint,c1boolean); CREATETABLE postgres=#insertintocas_testvalues(1,int'1'); INSERT01
2、如果系统中没有两种类型转换的CAST规则，那么我们需要自定义一个。
例如
postgres=#createcast(textastimestamp)withinoutasASSIGNMENT; CREATECAST Listofcasts Sourcetype|Targettype|Function|Implicit? -----------------------------+-----------------------------+---------------------------+--------------- text|timestampwithouttimezone|(binarycoercible)|inassignment
这样就可以自动将TEXT转换为TIMESTAMP了。
postgres=#insertintotbl123values(1,text'2017-01-0110:00:00'); INSERT01 postgres=#select*fromtbl123; id|crt_time ----+--------------------- 1|2017-01-0110:00:00 (1row)
删掉这个转换，就会报错。
postgres=#dropcast(textastimestamp); DROPCAST postgres=#insertintotbl123values(1,text'2017-01-0110:00:00'); ERROR:column"crt_time"isoftypetimestampwithouttimezonebutexpressionisoftypetext LINE1:insertintotbl123values(1,text'2017-01-0110:00:00'); ^ HINT:Youwillneedtorewriteorcasttheexpression.
3、如果没有内置的转换函数，我们可能需要自定义转换函数来支持这种转换。
例子
自定义一个函数，用于输入TEXT，返回TIMESTAMPTZ
postgres=#createorreplacefunctioncast_text_to_timestamp(text)returnstimestamptzas$$ selectto_timestamp($1,'yyyy-mm-ddhh24:mi:ss'); $$languagesqlstrict; CREATEFUNCTION
建立规则
postgres=#createcast(textastimestamptz)withfunctioncast_text_to_timestampasASSIGNMENT; CREATECAST postgres=#\dC Listofcasts Sourcetype|Targettype|Function|Implicit? -----------------------------+-----------------------------+---------------------------+--------------- text|timestampwithtimezone|cast_text_to_timestamp|inassignment
现在，输入TEXT，就可以自定转换为timestamptz了。
postgres=#createtabletbl1234(idint,crt_timetimestamptz); CREATETABLE postgres=#insertintotbl1234values(1,text'2017-01-0110:10:10'); INSERT01
当然，这些类型实际上内部都有内部的存储格式，大多数时候，如果存储格式通用，就可以直接使用INOUT来转换，不需要写转换函数。
仅仅当两种类型在数据库的内部存储格式不一样的时候，需要显示的写函数来转换。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。