Materialize Logo

CAST Function and Operator

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

Signatures

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.

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
array1 text Assignment
bigint bool Explicit
bigint int Assignment
bigint float Implicit
bigint numeric Implicit
bigint real Implicit
bigint text 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
int bigint Implicit
int bool Explicit
int float Implicit
int numeric Implicit
int oid Implicit
int real Implicit
int text 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
numeric bigint Assignment
numeric float Implicit
numeric int Assignment
numeric real Implicit
numeric text Assignment
oid int Assignment
oid text Explicit
real bigint Assignment
real float Implicit
real int Assignment
real numeric Assignment
real text Assignment
record text 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 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
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.

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
Did this info help?
Yes No