CAST function and operator

The cast function and operator return a value converted to the specified type.

Signatures

Parameter	Type	Description
val	Any	The value you want to convert.
type	Typename	The return value’s type.

The following special syntax is permitted if val is a string literal:

Return value

cast returns the value with the type specified by the type parameter.

Details

Valid casts

Cast context defines when casts may occur.

Cast context	Definition	Strictness
Implicit	Values are automatically converted. For example, when you add `int4` to `int8`, the `int4` value is automatically converted to `int8`.	Least
Assignment	Values of one type are converted automatically when inserted into a column of a different type.	Medium
Explicit	You must invoke `CAST` deliberately.	Most

Casts allowed in less strict contexts are also allowed in stricter contexts. That is, implicit casts also occur by assignment, and both implicit casts and casts by assignment can be explicitly invoked.

Source type	Return type	Cast context
`array`¹	`text`	Assignment
`bigint`	`bool`	Explicit
`bigint`	`int`	Assignment
`bigint`	`float`	Implicit
`bigint`	`numeric`	Implicit
`bigint`	`real`	Implicit
`bigint`	`text`	Assignment
`bigint`	`uint2`	Assignment
`bigint`	`uint4`	Assignment
`bigint`	`uint8`	Assignment
`bool`	`int`	Explicit
`bool`	`text`	Assignment
`bytea`	`text`	Assignment
`date`	`text`	Assignment
`date`	`timestamp`	Implicit
`date`	`timestamptz`	Implicit
`float`	`bigint`	Assignment
`float`	`int`	Assignment
`float`	`numeric`²	Assignment
`float`	`real`	Assignment
`float`	`text`	Assignment
`float`	`uint2`	Assignment
`float`	`uint4`	Assignment
`float`	`uint8`	Assignment
`int`	`bigint`	Implicit
`int`	`bool`	Explicit
`int`	`float`	Implicit
`int`	`numeric`	Implicit
`int`	`oid`	Implicit
`int`	`real`	Implicit
`int`	`text`	Assignment
`int`	`uint2`	Assignment
`int`	`uint4`	Assignment
`int`	`uint8`	Assignment
`interval`	`text`	Assignment
`interval`	`time`	Assignment
`jsonb`	`bigint`	Explicit
`jsonb`	`bool`	Explicit
`jsonb`	`float`	Explicit
`jsonb`	`int`	Explicit
`jsonb`	`real`	Explicit
`jsonb`	`numeric`	Explicit
`jsonb`	`text`	Assignment
`list`¹	`list`	Implicit
`list`¹	`text`	Assignment
`map`	`text`	Assignment
`mz_aclitem`	`text`	Explicit
`numeric`	`bigint`	Assignment
`numeric`	`float`	Implicit
`numeric`	`int`	Assignment
`numeric`	`real`	Implicit
`numeric`	`text`	Assignment
`numeric`	`uint2`	Assignment
`numeric`	`uint4`	Assignment
`numeric`	`uint8`	Assignment
`oid`	`int`	Assignment
`oid`	`text`	Explicit
`real`	`bigint`	Assignment
`real`	`float`	Implicit
`real`	`int`	Assignment
`real`	`numeric`	Assignment
`real`	`text`	Assignment
`real`	`uint2`	Assignment
`real`	`uint4`	Assignment
`real`	`uint8`	Assignment
`record`	`text`	Assignment
`smallint`	`bigint`	Implicit
`smallint`	`float`	Implicit
`smallint`	`int`	Implicit
`smallint`	`numeric`	Implicit
`smallint`	`oid`	Implicit
`smallint`	`real`	Implicit
`smallint`	`text`	Assignment
`smallint`	`uint2`	Assignment
`smallint`	`uint4`	Assignment
`smallint`	`uint8`	Assignment
`text`	`bigint`	Explicit
`text`	`bool`	Explicit
`text`	`bytea`	Explicit
`text`	`date`	Explicit
`text`	`float`	Explicit
`text`	`int`	Explicit
`text`	`interval`	Explicit
`text`	`jsonb`	Explicit
`text`	`list`	Explicit
`text`	`map`	Explicit
`text`	`numeric`	Explicit
`text`	`oid`	Explicit
`text`	`real`	Explicit
`text`	`time`	Explicit
`text`	`timestamp`	Explicit
`text`	`timestamptz`	Explicit
`text`	`uint2`	Explicit
`text`	`uint4`	Assignment
`text`	`uint8`	Assignment
`text`	`uuid`	Explicit
`time`	`interval`	Implicit
`time`	`text`	Assignment
`timestamp`	`date`	Assignment
`timestamp`	`text`	Assignment
`timestamp`	`timestamptz`	Implicit
`timestamptz`	`date`	Assignment
`timestamptz`	`text`	Assignment
`timestamptz`	`timestamp`	Assignment
`uint2`	`bigint`	Implicit
`uint2`	`float`	Implicit
`uint2`	`int`	Implicit
`uint2`	`numeric`	Implicit
`uint2`	`real`	Implicit
`uint2`	`text`	Assignment
`uint2`	`uint4`	Implicit
`uint2`	`uint8`	Implicit
`uint4`	`bigint`	Implicit
`uint4`	`float`	Implicit
`uint4`	`int`	Assignment
`uint4`	`numeric`	Implicit
`uint4`	`real`	Implicit
`uint4`	`text`	Assignment
`uint4`	`uint2`	Assignment
`uint4`	`uint8`	Implicit
`uint8`	`bigint`	Assignment
`uint8`	`float`	Implicit
`uint8`	`int`	Assignment
`uint8`	`real`	Implicit
`uint8`	`uint2`	Assignment
`uint8`	`uint4`	Assignment
`uuid`	`text`	Assignment

¹ Arrays and lists are composite types subject to special constraints. See their respective type documentation for details.

² Casting a float to a numeric can yield an imprecise result due to the floating point arithmetic involved in the conversion.

Examples

SELECT INT '4';

 ?column?
----------
         4

SELECT CAST (CAST (100.21 AS numeric(10, 2)) AS float) AS dec_to_float;

 dec_to_float
--------------
       100.21

SELECT 100.21::numeric(10, 2)::float AS dec_to_float;

 dec_to_float
--------------
       100.21

Data Types