3.12. Casts

Casts are objects used to convert values between two different data types. PostgreSQL implements several type casts but the user has the freedom to create custom casts to provide, for instance, the conversion between user-defined types and built-in data types and vice-versa. Casts are simple objects in their configuration since the hard work is done by the conversion function itself. The editing dialog of this kind of object is detailed as follows:

Attribute Description
Cast Type Defines the cast type or mode. The Explicit indicates that cast must be explicitly called to perform the value conversion, for instance, the query SELECT CAST(10 AS float8), will force the conversion of the integer 10 to a float8 value.

The Implicit mode indicates that the cast is implicitly done as the following query: SELECT 2 + 10.25. In this case, the parser will convert the integer 2 to a numeric type value and perform the sum returning a floating-point result.

The Assignment mode will enable the cast to be done when assigning a value to a column. Let's assume the table tableA(id text), now by running INSERT INTO tableA(id) VALUES (20), the value 20 will be automatically converted to a string and assigned to column id. Note that the data type of the column is text not integer.

The modifier Input/Output indicates that the cast must convert the value using the output function of the source data type and pass the resulting string to the input function of the target type. Details about these functions can be found in the user-defined types section in this manual.
Conversion Function The function that will perform the value conversion. This function must have the following signature: typeB function(typeA, integer, boolean) to provide the conversion from typeA to typeB.
Source data type Configures the cast's source data type.
Target data type Configures the cast's target data type.

Apr 3, 2023 at 16:31