PostgreSQL 18 reference · Conditional and Type Expressions

PostgreSQL CAST and :: Type Conversion

Convert a value to another type with CAST(x AS type) or the PostgreSQL shorthand x::type. Invalid input raises an error.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

CAST(value AS type)  |  value::type

Parameter	Description
`value`	The value.
`type`	The target type, such as `integer`, `numeric(10,2)`, `date`, `text`, `jsonb`.

The converted value.

Convert types

SELECT '42'::integer + 1 AS a, CAST('2026-03-09' AS date) + 7 AS b, 7 / 2 AS int_div, 7::numeric / 2 AS num_div;

Output

 a  |     b      | int_div |      num_div
----+------------+---------+--------------------
 43 | 2026-03-16 |       3 | 3.5000000000000000
(1 row)

Run this on your computer — see “Get Started” for setup.

Check before casting (16+)

SELECT pg_input_is_valid('12abc', 'integer') AS ok, pg_input_is_valid('2026-02-30', 'date') AS date_ok;

Output

 ok | date_ok
----+---------
 f  | f
(1 row)

Run this on your computer — see “Get Started” for setup.

PG_TYPEOF() · Return the data type of a value as a name, such as integer, numeric or text. Useful for debugging casts.
TO_CHAR() · Format a date, timestamp or number as text with a pattern such as 'DD Mon YYYY' or 'FM999,990.00'.
TO_DATE() · Parse text into a date using a format pattern, such as to_date('09/03/2026', 'DD/MM/YYYY').
SQL Aggregate Functions · tutorial