Type Casting

A cast converts a value from one type to another. Write it as CAST(value AS type) or with the shorthand value::type. Casting goes through a single conversion path, so the same rules apply whether the value comes from a literal, a column, or an expression.

SELECT CAST('42' AS integer);   -- 42
SELECT 3.7::integer;            -- 4   (rounds to nearest)
SELECT 42::text;               -- '42'
SELECT now()::date;            -- today's date (time dropped)

Numeric conversions

Integer and floating-point values convert freely between widths and to/from numeric. Converting a fractional value to an integer rounds to the nearest integer; converting between numeric scales rounds to the target scale.

SELECT 3.7::integer;            -- 4
SELECT 2.5::integer;            -- 3   (rounds to nearest)
SELECT 1.005::numeric(4,2);     -- 1.01
SELECT 100::double precision;   -- 100

Overflow always errors — it never truncates

Casting a value into a type too small to hold it raises 22003 numeric_value_out_of_range. The value is never silently wrapped or truncated, at any width, in any position (literal, nested cast, or column):

SELECT 32768::smallint;        -- ERROR: smallint out of range
SELECT (-32769)::smallint;     -- ERROR: smallint out of range
SELECT 99999999999::integer;   -- ERROR: integer out of range
SELECT 1e40::real;             -- ERROR: value out of range for type real
SELECT 999999::numeric(5,0);   -- ERROR: numeric field overflow

Text conversions

Any value can be cast to text. Casting from text parses and validates the input for the target type; invalid input raises 22P02 invalid_text_representation (or 22007/22008 for dates and timestamps).

SELECT '3.14'::numeric;             -- 3.14
SELECT '2021-03-04'::date;          -- 2021-03-04
SELECT 'true'::boolean;             -- t  (also 't','yes','on','1')
SELECT 'abc'::integer;              -- ERROR: invalid input syntax for integer
SELECT '2021-13-40'::date;          -- ERROR: invalid date

char(n) and varchar(n) enforce their length on cast; char(n) is blank-padded to width.

Boolean and integer

boolean casts to and from integer (true → 1, false → 0, 0 → false, non-zero → true). A boolean predicate (e.g. in WHERE) must be a boolean expression — an integer column is not implicitly truthy; convert it with (n <> 0).

SELECT 1::boolean;        -- t
SELECT true::integer;     -- 1
SELECT (count <> 0) FROM t;  -- use an explicit comparison in predicates

Date and time

date and timestamp convert between each other (timestamp::date drops the time; date::timestamp adds midnight) and parse from text. To convert an epoch number to a timestamp, use the to_timestamp(seconds) function rather than a cast — a bare integer has no single calendar meaning.

SELECT '2021-03-04 05:06:07'::timestamp::date;  -- 2021-03-04
SELECT to_timestamp(1614834367);                -- 2021-03-04 05:06:07+00

Conversions that aren’t defined

Some type pairs have no meaningful conversion and raise 42846 cannot_coerce. Use the dedicated function for the interpretation you want instead of a cast:

Goal	Use
epoch number → timestamp	to_timestamp(secs)
timestamp → epoch seconds	EXTRACT(EPOCH FROM ts)
number → its byte form	decode(...) / the typed *send functions
bytea → readable text	convert_from(b, 'UTF8') (a plain cast yields the \x… hex form)

NULL

Casting NULL to any type yields NULL — the cast never errors on a NULL input.

SELECT CAST(NULL AS integer);   -- NULL