CREATE CAST: defines a new cast that specifies how to perform a conversion between two data types.
CREATE CAST (source_type AS target_type) WITH INOUT [ AS ASSIGNMENT | AS IMPLICIT ]
- source_type: the name of the source data type of the cast.
- target_type: the name of the target data type of the cast.
- WITH INOUT: specifies that the cast is an I/O conversion cast, 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.
- AS ASSIGNMENT: specifies that the cast can be invoked implicitly in assignment contexts.
- AS IMPLICIT: specifies that the cast can be invoked implicitly in any context.
Generally, if a filter statement contains a comparison operator and the values on each side are of different data types, such as STRING and NUMERIC, the statement may fail because data type conversion is not supported. To execute such a statement, you can create a cast to specify how to perform a conversion between the STRING and NUMERIC types. An example is as follows:
CREATE TABLE test_cast(id text); INSERT INTO test_cast VALUES(888); SELECT * FROM test_cast aa WHERE id > 888; CREATE CAST (text AS integer) WITH INOUT AS IMPLICIT; SELECT * FROM test_cast aa WHERE id > 888;
For more information about the CREATE CAST statement, see CREATE CAST of PostgreSQL.