CAST function and operator

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


CAST ( val AS type )
val :: type
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:

type val

Return value

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


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
array1 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 numeric2 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
list1 list Implicit
list1 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

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

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



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

SELECT 100.21::numeric(10, 2)::float AS dec_to_float;
Back to top ↑