Cast Conversion Functions

Casting and conversion functions are available for all built-in system data types.

Casting functions

FunctionReturn typeDescriptionExampleResult
CAST(col_expr AS data type)same as requested data typeconverts the value of the col_expr into the specified data typecast(123 as varchar(10))'123'
CONVERT(col_expr , data type)same as requested data typeconverts the value of the col_expr into the specified data typeconvert(123, decimal(10,3))123.000


select cast(true as smallint);
select cast(42 as int);
select cast(123.45 as real);
select cast('123.45' as double precision);
select cast(23.45 as decimal(5,2));    -- precision of 5 digits of which 2 decimal digits

select cast('2020-07-29' as date);
select cast('17:44:59' as time);
select cast('17:44:59.123456' as time);
select cast('2020-07-29 17:44:59' as timestamp);
select cast('2020-07-29T17:44:59' as timestamp);
select cast('2020-07-29 17:44:59.123456' as timestamp);
select cast('17:44:59.321+01:30' as timetz);
select cast('2020-07-29 17:44:59.321+01:30' as timestamptz);
select cast('1234' as interval month);
select cast('86400.123' as interval second);

select cast('a4cd' as blob);    -- use 2 hex digits per byte, so 'a4cd' is 2 bytes long.
select cast('abcde' as clob);
select cast('' as inet);
select cast(r'{"a":[1,2,4]}' as json);
select cast('' as url);
select cast('e31960fb-dc8b-452d-ab30-b342723e756a' as uuid);

-- or using convert instead of cast:
select convert('a4cd' , blob);
select convert('abcde' , clob);
select convert('' , inet);
select convert(r'{"a":[1,2,4]}' , json);
select convert('' , url);
select convert('e31960fb-dc8b-452d-ab30-b342723e756a' , uuid);

Casting operators for character string literals

x'hexadecimals'convert string value (pairs of hexadecimals) to binary string (blob)x'abcd'
blob 'hexadecimals'convert string value (pairs of hexadecimals) to blob typeblob 'abcd'
inet 'valid inet address string'convert string value to inet typeinet ''
json 'valid json string'convert string value to json typejson '{"a":[1,2,4]}'
url 'valid url string'convert string value to url typeurl ''
uuid 'valid uuid string'convert string value to uuid typeuuid 'e31960fb-dc8b-452d-ab30-b342723e756a'
E'string with backslash escapes'convert backslash escapes (such as: \f \t \n \b \u etc) in string as specific (control) characterse'A\fB\tC\n\Z'
R'any string'NO conversion of backslash escapes is done. Treat string characters as provided, so rawr'A\fB\tC\n\Z'