Type Casting

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)