Execute the CREATECAST statement to convert data types -

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.