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.)

From Data Type To Data Type(s)
SMALLINT BOOLEAN
INTEGER BOOLEAN, DATE, TIMESTAMP, TIMESTAMPTZ
BIGINT BOOLEAN, DATE, TIMESTAMP, TIMESTAMPTZ
DECIMAL BOOLEAN, DECIMAL
REAL BOOLEAN, DECIMAL
DOUBLE PRECISION BOOLEAN, DECIMAL
BOOLEAN SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION

Explicit casts yield the values 0 and 1.

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)