Type Conversion in SQL: CAST and the :: Operator in PostgreSQL

Type conversion shows up everywhere: you pull a number out of a text column, turn a string into a date, or round a numeric down to an integer for a report. SQL gives you the standard CAST plus PostgreSQL's handy :: shorthand. A cast does not change the stored data; it only tells the planner how to read an expression. The thing to keep straight is the difference between a cast that just relabels a type safely and one that can take down the whole query on a single bad row.

CAST vs ::

The SQL standard defines the functional form CAST(value AS type). Prefer it in portable queries and in code that may move from PostgreSQL to MySQL or ClickHouse, because all three engines understand it:

SELECT CAST(amount AS integer) AS amount_int
FROM orders
WHERE status = 'paid';

PostgreSQL adds the :: operator, which is the exact same cast written shorter. In PostgreSQL-only code it shows up constantly:

-- These two lines are equivalent in PostgreSQL
SELECT amount::integer FROM orders;
SELECT CAST(amount AS integer) FROM orders;

Useful details that bite in real queries:

• :: binds very tightly, tighter than arithmetic: count(*)::numeric / total casts only count(*), and the division runs afterward, so the whole fraction is not cast.
• To cast a compound expression, wrap it in parentheses: (a + b)::int.
• :: is a PostgreSQL extension, not standard SQL. MySQL has no such operator, so keep CAST for portable queries.

Common casts

The most frequent cases are text to a number, text to a date, and a computed value to a report-friendly type. On a tiny example everything looks safe, but one of these hides a rounding trap:

-- Text to integer and to date
SELECT '42'::integer AS qty,
       '2026-06-17'::date AS signup_day;

-- Casting numeric to integer rounds to nearest, it does not truncate
SELECT CAST(99.99 AS integer) AS rounded;   -- see the note below

-- Render a timestamp column as text
SELECT created_at::text FROM users LIMIT 5;

The trap is semantic, not syntactic. CAST(99.99 AS integer) in PostgreSQL rounds to the nearest integer, so you get 100, not 99; the fractional part is not dropped. If you actually want truncation toward zero, do not cast directly -- use trunc(99.99)::integer. In a report the gap between rounding and truncation is visible, for example in average order value per country:

SELECT u.country,
       CAST(AVG(o.amount) AS integer) AS avg_amount
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country;

Implicit vs explicit casts

PostgreSQL performs some casts for you, implicitly, but you should not lean on that when designing a query. It compares numbers of compatible types on its own, yet text against a number in the same condition it refuses:

-- Implicit: integer compared with numeric just works
SELECT * FROM employees WHERE salary > 50000;

-- This FAILS in PostgreSQL: text vs integer, no implicit cast
SELECT * FROM orders WHERE status = 1;

-- Explicit fix
SELECT * FROM orders WHERE status = CAST(1 AS text);

MySQL is more lenient here: it silently coerces status = 1, turning the string into a number by its own rules. That is convenient right up until the first odd report, because a string that does not look like a number becomes 0 and quietly skews the result set. PostgreSQL is stricter and demands an explicit CAST, but the error surfaces immediately where the data does not match the model, instead of in a finished report.

Cast failures and how to guard against them

The main risk of casting is dirty strings. A single row with non-numeric content brings down the whole SELECT, even if the other millions of values convert perfectly:

-- Fails on the first row where email holds non-numeric text
SELECT email::integer FROM users;
-- ERROR: invalid input syntax for type integer

Now to clear up a common myth. A plain scalar CAST in PostgreSQL has no ON ERROR or DEFAULT clause: the next query does not work in either PostgreSQL 16 or 17 -- it returns a syntax error, not NULL on the bad rows. Do not copy it into your code; this is an example of what not to do:

-- WRONG: this is NOT valid SQL, a scalar CAST has no ON ERROR clause
SELECT CAST(amount AS integer DEFAULT NULL ON ERROR) AS amount_int
FROM orders;

The ON ERROR clause does exist in PostgreSQL, but only inside the SQL/JSON functions JSON_VALUE, JSON_QUERY and JSON_TABLE added in version 17, and it has nothing to do with a scalar type cast. So for an ordinary CAST there is one working path: filter for the valid format first with a regular expression, then cast the type:

SELECT (amount::text)::numeric AS clean_amount
FROM orders
WHERE amount::text ~ '^[0-9]+(\.[0-9]+)?$';

On PostgreSQL 16+ you can also test a value before casting with pg_input_is_valid(text, type), which returns a boolean instead of throwing:

-- Postgres 16+: skip rows that would fail the cast
SELECT amount::integer
FROM orders
WHERE pg_input_is_valid(amount::text, 'integer');

One more subtlety: NULL casts to a NULL of the target type without error, so a CAST is not afraid of missing values as such. What breaks the query is not absent values but non-empty strings that do not parse as the target type. So the prefilter must check the content of the string, not just whether a value is present, and you should also reject the empty string '' before casting.

MySQL and ClickHouse

In MySQL you also write a cast as CAST(x AS type), plus there is the near-equivalent CONVERT(x, type), but the target types and behavior differ from PostgreSQL:

• For an integer, MySQL uses CAST(x AS SIGNED) or UNSIGNED; older versions did not accept the keyword INTEGER in CAST.
• CONVERT handles not only types but also character sets: CONVERT(name USING utf8mb4) re-encodes the string.
• On a failed cast MySQL does not fail by default; it returns 0 or NULL with a warning, so the error gets quietly lost in the logs instead of being an explicit failure as in PostgreSQL.

-- MySQL flavor
SELECT CAST(amount AS SIGNED) AS amount_int,
       CONVERT(created_at, DATE) AS day
FROM orders;

ClickHouse understands both CAST(x AS type) and the :: operator, but for typical conversions people more often reach for the specialized function family toInt32, toDate, toFloat64. In streams of dirty data, where PostgreSQL would make you write a prefilter, ClickHouse offers ready-made variants with the suffixes OrNull and OrZero that return NULL or zero instead of raising an exception:

-- ClickHouse: never throws, returns NULL on bad input
SELECT toInt32OrNull(amount) FROM orders;

Short rule: take CAST when portability between PostgreSQL, MySQL and ClickHouse matters, and :: when you write pure PostgreSQL code and value brevity. Either way, make the cast explicit, remember the rounding when going from numeric to integer, and screen out dirty strings up front. Each engine has its own safe mechanism: in PostgreSQL it is a regex prefilter (or pg_input_is_valid on 16+), in ClickHouse the OrNull functions -- and you should not wait for a non-existent ON ERROR on a scalar CAST.