PostgreSQL CAST: Convert Types With CAST and ::

TL;DR: PostgreSQL CAST converts a value from one data type to another. Write it as CAST(value AS type) (SQL standard) or value::type (PostgreSQL shorthand); both do exactly the same thing. A cast fails with an error if the value isn't valid for the target type, so on PostgreSQL 16 and later check risky text first with pg_input_is_valid(value, 'type').

CAST in PostgreSQL is how you change a value's data type inside a query: SELECT CAST('42' AS integer); and SELECT '42'::integer; both return the integer 42. The PostgreSQL 18 manual (Section 4.2.9) states the two syntaxes are equivalent: CAST conforms to the SQL standard, and :: is historical PostgreSQL usage that most Postgres code uses because it is shorter. This page gives the exact cast for every common conversion (text to integer, string to date or timestamp, integer to text, numeric to integer, JSON to JSONB, text to boolean), the error codes each one throws, the rounding rule that silently changes numbers, and the PostgreSQL 16 functions that let you test a cast before it breaks a query.

What is the syntax of CAST in PostgreSQL?

PostgreSQL accepts three spellings of a type cast:

CAST ( expression AS type )  , SQL standard, portable
expression::type             , PostgreSQL shorthand
type ( expression )          , function-like, avoid

Syntax | Example | Portable? | Notes

CAST(x AS type) | CAST(price AS integer) | Yes (MySQL, SQL Server, Oracle) | Use in SQL that must run on several databases

x::type | price::integer | No, PostgreSQL only | Binds tightly: -1::text casts 1 first, so write (-1)::text

type(x) | float8(price) | No | Breaks for double precision; interval, time and timestamp need double quotes; the manual says it "should probably be avoided"

The manual also notes that a cast applied to an untyped string literal ('2026-10-06'::date) is not a run-time conversion but the initial assignment of a type to the constant, so it succeeds for any type as long as the string is valid input for that type.

How do you convert a string to an integer in PostgreSQL?

Cast text to integer (4 bytes), bigint (8 bytes) or smallint (2 bytes):

SELECT '42'::integer;                , 42
SELECT CAST(order_ref AS bigint) FROM imports;
SELECT (payload->>'quantity')::int FROM events;  , text from JSON

Three things make this cast fail:

Input | Error | SQLSTATE

'abc'::integer or ''::integer | invalid input syntax for type integer | 22P02

'12.5'::integer | invalid input syntax for type integer (integer input does not accept decimals) | 22P02

'42000000000'::integer | value "42000000000" is out of range for type integer | 22003

For decimal strings, go through numeric: '12.5'::numeric::integer returns 13. For values over 2,147,483,647, use bigint, whose range is -9223372036854775808 to +9223372036854775807. Empty strings are common in CSV imports; turn them into NULL first with NULLIF(col, '')::integer.

How do you cast a string to a date or timestamp in PostgreSQL?

Use ::date, ::timestamp or ::timestamptz for ISO 8601 strings, and to_date() / to_timestamp() for any other layout:

SELECT '2026-10-06'::date;                             , 2026-10-06
SELECT '2026-10-06 14:30:00'::timestamp;               , 2026-10-06 14:30:00
SELECT '2026-10-06 14:30:00+05:30'::timestamptz;       , stored as UTC instant
SELECT to_date('05 Dec 2000', 'DD Mon YYYY');          , 2000-12-05 (manual's example)
SELECT to_timestamp('06/10/2026 14:30', 'DD/MM/YYYY HH24:MI');

An unparseable string fails with invalid input syntax for type date (22007, invalid_datetime_format); a valid layout with impossible values such as '2026-13-01'::date fails with date/time field value out of range (22008, datetime_field_overflow). Ambiguous inputs like '06/10/2026' are read according to the DateStyle setting, which is why to_date() with an explicit pattern is safer for anything that isn't ISO format.

The reverse direction, timestamp to date, drops the time: now()::date returns today. For timestamptz, the resulting date depends on the session TimeZone, so a timestamptz at 23:30 UTC becomes the next day in Asia/Kolkata. Date arithmetic after the cast is covered in our DATEDIFF in PostgreSQL and DATEADD in PostgreSQL guides.

How do you cast an integer or number to text in PostgreSQL?

::text (or ::varchar) converts any number to its default text form; to_char() controls the format:

SELECT 42::text;                             , '42'
SELECT 'Order #' || order_id::text FROM orders;
SELECT to_char(1234567.891, 'FM9,999,999.00');, '1,234,567.89'
SELECT to_char(now(), 'YYYY-MM-DD');         , date as text

You often don't need an explicit cast for concatenation, because || accepts one non-text argument, but being explicit avoids operator does not exist errors (42883) when both sides are non-text.

How does PostgreSQL round when casting numeric or double to integer?

Casting a fractional number to integer rounds, it does not truncate, and the tie rule depends on the source type. The PostgreSQL manual (Section 8.1) documents that numeric rounds ties away from zero, while real and double precision round ties to the nearest even number on most machines:

Expression | Result | Why

2.5::integer | 3 | 2.5 is a numeric literal, ties away from zero

2.5::float8::integer | 2 | double precision, ties to even

3.5::float8::integer | 4 | ties to even

(-2.5)::integer | -3 | away from zero

trunc(2.9)::integer | 2 | explicit truncation

floor(-2.5)::integer | -3 | explicit floor

This is a frequent source of off-by-one totals when a column is double precision in one table and numeric in another. If you need truncation, say so with trunc() or floor(). Casting to a constrained numeric also rounds: numeric(10,2) rounds to 2 decimal places, then errors (22003) if the integer part has more than 8 digits.

The opposite cast fixes integer division: SELECT 5 / 2; returns 2, while SELECT 5::numeric / 2; returns 2.5000000000000000. Cast one operand before dividing when you compute rates like conversion percentages.

How do you cast text to boolean, and JSON to JSONB?

Text to boolean accepts true/false, yes/no, on/off, 1/0 and unique prefixes such as t, f, y, n; case doesn't matter and surrounding whitespace is ignored (manual Section 8.6):

SELECT 'yes'::boolean, 'OFF'::boolean, ' t '::boolean;  , true, false, true
SELECT 1::boolean;                                      , true (integer to boolean)

'maybe'::boolean fails with 22P02.

For JSON, ::jsonb converts json or text into the binary, indexable form, and ->> extracts a field as text that you then cast:

SELECT '{"plan":"pro","seats":"12"}'::jsonb;
SELECT (data->>'seats')::int AS seats FROM accounts;
SELECT (data->>'created')::timestamptz FROM accounts;

Wrap the ->> expression in parentheses: data->>'seats'::int casts the key 'seats', not the result, and fails.

How do you cast safely without failing the whole query?

On PostgreSQL 16 and later, use pg_input_is_valid(text, type) to test a value before casting it. It was added in PostgreSQL 16 together with pg_input_error_info(), which returns the error message and SQLSTATE that the cast would raise:

 Cast only the rows that are valid, NULL for the rest
SELECT raw_amount,
       CASE WHEN pg_input_is_valid(raw_amount, 'numeric(12,2)')
            THEN raw_amount::numeric(12,2)
       END AS amount
FROM staging_payments;

 See why a value fails
SELECT * FROM pg_input_error_info('42000000000', 'integer');
 message: value "42000000000" is out of range for type integer, sql_error_code: 22003

The manual's examples: pg_input_is_valid('42', 'integer') returns t, pg_input_is_valid('42000000000', 'integer') returns f, and pg_input_is_valid('1234.567', 'numeric(7,4)') returns f. These functions rely on "soft" errors in the type's input function; built-in types support them, but a custom type that hasn't been updated will still abort the transaction.

On PostgreSQL 15 and earlier there is no built-in safe cast. The usual options are a regular-expression guard (CASE WHEN col ~ '^-?\d+$' THEN col::bigint END, which still misses overflow) or a small PL/pgSQL function that catches the exception and returns NULL.

What is the difference between CAST and ::, and when does PostgreSQL cast implicitly?

CAST(x AS t) and x::t compile to the same operation, so there is no performance difference; pick CAST for portability and :: for brevity. PostgreSQL applies a cast implicitly only when the cast is marked "OK to apply implicitly" in the system catalogs (for example integer to bigint or numeric). Others, like text to integer, must be explicit, which is why WHERE text_col = 5 raises operator does not exist: text = integer (42883) instead of guessing. You can list the casts that exist with SELECT * FROM pg_cast; and define new ones with CREATE CAST; a requested conversion with no cast defined fails with cannot cast type ... to ... (42846, cannot_coerce).

Letting the database do the casting

Most cast errors show up in reporting queries against messy columns: amounts stored as text, dates as strings, JSON fields that need ->> plus a cast. AI for Database connects to PostgreSQL, reads the column types from your schema, writes the casts into the SQL it generates and shows you the query, so "average order value by month" works even when amount is a varchar. More PostgreSQL references: CASE WHEN in PostgreSQL and our PostgreSQL glossary entry.