Explicit Casting ​

This section identifies data type conversions that require explicit casts and describes functions that you can use for explicit casting.

Required Explicit Casts ​

The following table lists data type combinations that must be cast explicitly; implicit casts are not supported for these combinations. (Any combination that supports implicit casting also supports explicit casting.)

Note: Explicit casts to DECIMAL require a declaration of the precision and scale: (p,s)

CAST Function ​

You can explicitly cast an expression, such as a column or the result of a function, to a specific data type.

CAST ( expression AS type )
expression::type

See SQL Data Types for information about supported types.

For example, the following queries are equivalent:

premdb=# select avg(capacity)::int from team;
  avg  
-------
 29982
(1 row)

premdb=# select cast(avg(capacity) as int) from team;
  avg  
-------
 29982
(1 row)

For example, to compare a character string (name) with an integer (teamid):

premdb=# select name from team where name=teamid::varchar(30);
 name 
------
(0 rows)

INT2, INT4, and INT8 Functions ​

You can use the following functions to coerce numeric values to integer data types:

• INT2(expression): cast the expression to a SMALLINT type (16-bit)
• INT4(expression): cast the expression to an INTEGER type (32-bit)
• INT8(expression): cast the expression to a BIGINT type (64-bit)

For example:

premdb=# select int2(seasonid), int4(htid), int8(atid) from match limit 3;
 int2 | int4 | int8 
------+------+------
   1 |    2 |   52
   1 |    2 |   55
   1 |    2 |   63
(3 rows)

FLOAT4 and FLOAT8 Functions ​

You can use the following functions to coerce numeric values to floating-point data types:

• FLOAT4(expression): cast the expression to a REAL type (32-bit)
• FLOAT8(expression): cast the expression to a DOUBLE PRECISION type (64-bit)

For example:

premdb=# select float4(avg_att), float8(capacity) from team limit 3;
 float4 | float8 
--------+--------
 59.944 |  60260
  33.69 |  42785
     0 |  23009
(3 rows)

Parent topic:Data Type Casting