背景
PostgreSQL是一個強類型數(shù)據(jù)庫，因此你輸入的變量、常量是什么類型，是強綁定的，例如
在調(diào)用操作符時，需要通過操作符邊上的數(shù)據(jù)類型，選擇對應(yīng)的操作符。
在調(diào)用函數(shù)時，需要根據(jù)輸入的類型，選擇對應(yīng)的函數(shù)。
如果類型不匹配，就會報操作符不存在，或者函數(shù)不存在的錯誤。
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.
那么使用起來是不是很不方便呢？
PostgreSQL開放了類型轉(zhuǎn)換的接口，同時也內(nèi)置了很多的自動類型轉(zhuǎn)換。來簡化操作。
查看目前已有的類型轉(zhuǎn)換：
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)
如果你發(fā)現(xiàn)有些類型轉(zhuǎn)換沒有內(nèi)置，怎么辦呢？我們可以自定義轉(zhuǎn)換。
當(dāng)然你也可以使用這種語法，對類型進行強制轉(zhuǎn)換：
CAST(x AS typename)
or
x::typename
如何自定義類型轉(zhuǎn)換(CAST)
自定義CAST的語法如下：
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 ]
解釋：
1、WITH FUNCTION，表示轉(zhuǎn)換需要用到什么函數(shù)。
2、WITHOUT FUNCTION，表示被轉(zhuǎn)換的兩個類型，在數(shù)據(jù)庫的存儲中一致，即物理存儲一致。例如text和varchar的物理存儲一致。不需要轉(zhuǎn)換函數(shù)。
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，表示使用內(nèi)置的IO函數(shù)進行轉(zhuǎn)換。每一種類型，都有INPUT 和OUTPUT函數(shù)。使用這種方法，好處是不需要重新寫轉(zhuǎn)換函數(shù)。
除非有特殊需求，我們建議直接使用IO函數(shù)來進行轉(zhuǎn)換。
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，表示在賦值時，自動對類型進行轉(zhuǎn)換。例如字段類型為TEXT，輸入的類型為INT，那么可以創(chuàng)建一個 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，表示在表達式中，或者在賦值操作中，都對類型進行自動轉(zhuǎn)換。（包含了AS ASSIGNMENT，它只對賦值進行轉(zhuǎn)換）
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需要謹慎使用，為什么呢？因為操作符會涉及到多個算子，如果有多個轉(zhuǎn)換，目前數(shù)據(jù)庫并不知道應(yīng)該選擇哪個？
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應(yīng)該是非失真轉(zhuǎn)換轉(zhuǎn)換，例如從INT轉(zhuǎn)換為TEXT，或者int轉(zhuǎn)換為numeric。
而失真轉(zhuǎn)換，不建議使用as implicit，例如numeric轉(zhuǎn)換為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.
注意事項 + 例子
不能嵌套轉(zhuǎn)換。例子
1、將text轉(zhuǎn)換為date
錯誤方法
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;
嵌套轉(zhuǎn)換后出現(xiàn)死循環(huán)
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;
?column?
------------
2017-01-02
(1 row)
我們還可以直接使用IO函數(shù)來轉(zhuǎn)換：
postgres=# create cast (text as date) with inout as implicit;
CREATE CAST
postgres=# select text '2017-01-01' + 1;
?column?
------------
2017-01-02
(1 row)
補充：PostgreSQL 整型int與布爾boolean的自動轉(zhuǎn)換設(shè)置(含自定義cast與cast規(guī)則介紹)
背景
在使用數(shù)據(jù)庫時，經(jīng)常會遇到一些因為客戶端輸入的類型與數(shù)據(jù)庫定義的類型不匹配導(dǎo)致的錯誤問題。
例如數(shù)據(jù)庫定義的是布爾類型，而輸入的是整型：
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
又或者數(shù)據(jù)庫定義的是時間，用戶輸入的是字符串：
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.
從錯誤提示來看，數(shù)據(jù)庫已經(jīng)很清晰的告訴你為什么了。那么怎么讓數(shù)據(jù)庫自動轉(zhuǎn)換呢？
PostgreSQL有一個語法，支持數(shù)據(jù)類型的轉(zhuǎn)換（賦值、參數(shù)、表達式 等位置的自動轉(zhuǎn)換）。
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 ]
數(shù)據(jù)庫內(nèi)置了很多轉(zhuǎn)換法則：
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
類型的自動轉(zhuǎn)換實際上也是有一定的規(guī)則的，例如 賦值、參數(shù) 算是兩種規(guī)則。具體含義見如下文檔：
《PostgreSQL 自定義自動類型轉(zhuǎn)換(CAST)》
我們看到整型轉(zhuǎn)布爾是有內(nèi)置的轉(zhuǎn)換規(guī)則的，那么為什么沒有自動轉(zhuǎn)呢？
postgres=# \dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
integer | boolean | bool | no
和自動轉(zhuǎn)換的規(guī)則有關(guān)，no表示不會自動轉(zhuǎn)換，只有當(dāng)我們強制指定轉(zhuǎn)換時，才會觸發(fā)轉(zhuǎn)換的動作：
postgres=# select cast ((int '1') as boolean);
bool
------
t
(1 row)
pg_cast里面的context轉(zhuǎn)換為可讀的內(nèi)容(e表示no, a表示assignment, 否則表示implicit)
如果讓數(shù)據(jù)庫賦值時自動將字符串轉(zhuǎn)換為時間，自動將整型轉(zhuǎn)換為布爾
1、如果數(shù)據(jù)庫已經(jīng)內(nèi)置了轉(zhuǎn)換規(guī)則，那么可以通過更新系統(tǒng)表的方式，修改自動轉(zhuǎn)換規(guī)則。
例如，將這個INT轉(zhuǎn)BOOLEAN的規(guī)則，修改為assignment的規(guī)則。
postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype;
UPDATE 1
修改后，我們再查看這個轉(zhuǎn)換規(guī)則，就變成這樣了
\dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
integer | boolean | bool | in assignment
現(xiàn)在你可以將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、如果系統(tǒng)中沒有兩種類型轉(zhuǎn)換的CAST規(guī)則，那么我們需要自定義一個。
例如
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轉(zhuǎn)換為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)
刪掉這個轉(zhuǎn)換，就會報錯。
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、如果沒有內(nèi)置的轉(zhuǎn)換函數(shù)，我們可能需要自定義轉(zhuǎn)換函數(shù)來支持這種轉(zhuǎn)換。
例子
自定義一個函數(shù)，用于輸入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
建立規(guī)則
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
現(xiàn)在，輸入TEXT，就可以自定轉(zhuǎn)換為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
當(dāng)然，這些類型實際上內(nèi)部都有內(nèi)部的存儲格式，大多數(shù)時候，如果存儲格式通用，就可以直接使用INOUT來轉(zhuǎn)換，不需要寫轉(zhuǎn)換函數(shù)。
僅僅當(dāng)兩種類型在數(shù)據(jù)庫的內(nèi)部存儲格式不一樣的時候，需要顯示的寫函數(shù)來轉(zhuǎn)換。
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
您可能感興趣的文章:- Postgresql 賦予用戶權(quán)限和撤銷權(quán)限的實例
- postgresql限制某個用戶僅連接某一個數(shù)據(jù)庫的操作
- PostgreSQL用戶、數(shù)據(jù)庫及表的管理、操作與授權(quán)方式
- PostgreSQL 實現(xiàn)快速刪除一個用戶
- PostgreSQL報錯 解決操作符不存在的問題
- postgresql 賦權(quán)語句 grant的正確使用說明
