background
PostgreSQL is a strongly typed database, so what type of variables and constants you input are strongly bound, for example
When calling an operator, you need to select the corresponding operator by the data type on the operator's edge.
When calling a function, you need to select the corresponding function according to the type of input.
If the types do not match, an error that the operator does not exist or the function does not exist will be reported.
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.
So isn't it very inconvenient to use?
PostgreSQL opens the type conversion interface, and also has a lot of automatic type conversion built-in. To simplify operations.
Check out the current type conversion:
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)
What if you find that some type conversions are not built-in? We can customize the conversion.
Of course you can also use this syntax to cast the type:
CAST(x AS typename) or x::typename
How to customize type conversion (CAST)
The syntax for custom CAST is as follows:
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 ]
explain:
1. WITH FUNCTION, which indicates what function is needed for the conversion.
2. WITHOUT FUNCTION indicates that the two types converted are consistent in the database storage, that is, the physical storage is consistent. For example, the physical storage of text and varchar is consistent. No conversion function is required.
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 means using built-in IO functions for conversion. Each type has INPUT and OUTPUT functions. The advantage of using this method is that there is no need to rewrite the conversion function.
Unless there are special requirements, we recommend using IO functions directly for conversion.
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 means that when assigning values, the type is automatically converted. For example, the field type is TEXT and the input type is INT, then you can create a 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 means that the type is automatically converted in expressions or assignment operations. (Includes AS ASSIGNMENT, which only converts the 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. Note that AS IMPLICIT needs to be used with caution, why? Because operators involve multiple operators, if there are multiple conversions, the database currently does not know which one should be selected?
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.
Therefore, it is recommended to use AS IMPLICIT with caution. It is recommended that CAST using AS IMPLICIT should be a non-distortion conversion conversion, such as from INT to TEXT, or int to numeric.
For distortion conversion, it is not recommended to use as implicit, such as numeric conversion to 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.
Notes + Examples
Cannot nest conversions. example
1. Convert text to date
Error Method
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;
A dead loop occurs after nested conversion
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 ......
The correct way
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)
We can also use IO functions directly to convert:
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)
Supplement: Automatic conversion settings of PostgreSQL integer int and boolean (including custom cast and cast rules introduction)
background
When using a database, you often encounter some error problems caused by the type input by the client does not match the type defined by the database.
For example, the database defines a Boolean type, and the input is an integer:
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
Or the database defines time, and the user inputs a string:
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.
Judging from the error prompt, the database has clearly told you why. So how do you make the database automatically convert?
PostgreSQL has a syntax that supports conversion of data types (automatic conversion of assignment, parameters, expressions, etc.).
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 ]
There are many conversion rules built into the database:
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
There are actually certain rules for automatic conversion of types, such as assignment and parameter are two rules. The specific meanings are as follows:
《PostgreSQL Custom Automatic Type Conversion (CAST)》
We see that integer to Boolean has built-in conversion rules, so why isn't it automatic rotation?
postgres=# \dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
integer | boolean | bool | no
It is related to the rules of automatic conversion. No means that there will be no automatic conversion. Only when we force the conversion, the conversion action will be triggered:
postgres=# select cast ((int '1') as boolean); bool ------ t (1 row)
The context in pg_cast is converted into readable content (e means no, a means assignment, otherwise means implicit)
If you automatically convert the string to time when the database is assigned, the integer to a boolean will be automatically converted
1. If the database already has conversion rules built-in, then the automatic conversion rules can be modified by updating the system table.
For example, change the rule of INT to BOOLEAN to the rule of assignment.
postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype; UPDATE 1
After modification, we check the conversion rule again, and it becomes like this
\dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
integer | boolean | bool | in assignment
Now you can write int automatically as 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. If there is no CAST rule for two types of conversion in the system, then we need to customize one.
For example
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
This will automatically convert TEXT to 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)
If you delete this conversion, you will report an error.
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. If there is no built-in conversion function, we may need to customize conversion functions to support this conversion.
example
Customize a function to enter TEXT and return 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
Establish rules
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
Now, enter TEXT and you can customize it to 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
Of course, these types actually have internal storage formats. Most of the time, if the storage format is general, you can directly use INOUT to convert, without writing conversion functions.
Only when the internal storage formats of the two types are different in the database, the write function that needs to be displayed is converted.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.