CAST(value AS type) converts a value to another type, and value::type is PostgreSQL's shorter form of the same thing. '42'::integer and CAST('42' AS integer) both return the integer 42. A third form, type 'literal' (as in date '2026-11-17'), works for constants only.
The text '2026-11-17' becomes a date, so adding 30 gives the date 30 days later. 'yes' is one of the spellings boolean accepts, along with true, t, on and 1.
Convert text to a number or a date
Data loaded from CSV files or forms often arrives as text. Cast it before comparing or calculating, because compared as text, '10' sorts before '9':
Ordered as integers, 9 comes before 10, and the line totals are 40.5 and 120. Leading and trailing spaces are allowed (' 42 '::integer works), but any other stray character raises "invalid input syntax for type integer". For text in another format, such as '17/11/2026' or '1,234.50', use to_date and to_number with a format pattern.
Rounding and integer division
Casting a numeric to an integer rounds half away from zero. Casting a double precision value rounds half to even. Integer division drops the fraction:
The results are 3, -3, 2, 4, 3, 3.5 and 12. Use trunc() or floor() before the cast when you want to cut the fraction instead of rounding.
Casting to a smaller type checks the range: 70000::smallint fails with "smallint out of range", and 12.345::numeric(5, 2) rounds to 12.35.
Check the input before casting
A failed cast stops the whole statement. Since PostgreSQL 16, pg_input_is_valid(text, type) tests whether a cast would succeed, and pg_input_error_info returns the error message without raising it:
Only '42' is a valid integer. '2026-02-30' looks like a date but is not one, so valid_date is false for it too. For the NULL row every column is NULL, since casting NULL gives NULL of the new type.
When PostgreSQL casts for you
A quoted literal has no type until PostgreSQL sees where it is used. So WHERE id = '5' and '1' + 1 work, with the literal read as an integer. A value that already has a type is not converted for you. '1'::text + 1 fails with "operator does not exist: text + integer", and so does comparing a text column with an integer column. Add the cast yourself.
Writes are more lenient. An INSERT or UPDATE converts a value to the column's type when an assignment cast exists, so inserting the numeric 12.7 into an integer column stores 13.
To change a column's type for good, cast every row once with ALTER TABLE ... ALTER COLUMN ... TYPE ... USING:
ALTER TABLE imports ALTER COLUMN qty TYPE integer USING qty::integer;
The data types page lists the types you can cast to.
Frequently Asked Questions
What does :: mean in PostgreSQL?
It is PostgreSQL's cast operator: '42'::integer converts the text '42' to an integer. It does the same as the standard CAST('42' AS integer).
Should I use CAST or :: in PostgreSQL?
They behave the same. :: is shorter and common in PostgreSQL code. CAST(... AS ...) is standard SQL that MySQL, SQL Server and SQLite also accept, so use it in queries that must stay portable.
How do I cast without an error when the text is not a number?
Test it first. Since PostgreSQL 16, pg_input_is_valid('4x2', 'integer') returns false instead of raising an error, so CASE WHEN pg_input_is_valid(s, 'integer') THEN s::integer END gives NULL for bad input. On older versions, check the text with a regex such as s ~ '^-?\d+$' first.
Why does 7 / 2 return 3 in PostgreSQL?
Dividing two integers gives an integer and drops the fraction. Cast one side to numeric to get 3.5: 7::numeric / 2.