You can execute the CREATE CAST statement to convert data types. To execute this statement, you must be the owner of the source data type and be granted the superuser
permissions. This topic describes how to execute the CREATE CAST statement to convert data types.
Syntax
CREATE CAST (source_type AS target_type)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
The following table describes the parameters.
Parameter | Description |
source_type | The source data type. |
target_type | The destination data type. |
WITH INOUT | Indicates 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 | Indicates that the cast can be invoked implicitly in assignment contexts. |
AS IMPLICIT | Indicates that the cast can be invoked implicitly in any context. |
Examples:
In most cases, 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. You can execute the CREATE CAST
statement to create a cast that supports comparison operators. Sample statements:
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 PostgreSQL CREATE CAST.