CAST and ::

Converts a value to another data type.

Syntax

CAST(expression AS type)
expression::type                 -- PostgreSQL shorthand
type 'literal'                   -- typed literal, e.g. DATE '2025-03-01'

CAST(x AS numeric) and x::numeric are identical; :: is PostgreSQL-specific but very common.
Text → number/date conversion fails with invalid input syntax for type ... if any row has a bad value. Clean or filter first (e.g. WHERE code ~ '^[0-9]+$').
numeric::integer rounds (half away from zero): 2.5::integer is 3. Use trunc()/floor() to drop the fraction.
timestamp::date drops the time of day — the usual way to group by day.
'2025-02-30'::date is an error (date/time field value out of range).
Casting to numeric(p, s) rounds to s decimals and errors if the value does not fit.
NULL: casting NULL gives NULL of the target type.
Use casts to fix integer division: count(*)::numeric / total.

Examples

Common conversions starter

SELECT '42'::integer + 1 AS from_text,
       CAST('2025-03-15' AS date) + 10 AS date_plus_days,
       2.5::integer AS rounded_int,
       trunc(2.5) AS truncated,
       now()::date AS today,
       12.3456::numeric(6, 2) AS two_decimals;

date + integer adds days; ::integer rounds 2.5 to 3; trunc drops the fraction.

Timestamp to date for daily grouping shop

SELECT ordered_at::date AS order_day, count(*) AS orders
FROM orders
WHERE ordered_at >= '2025-11-20' AND ordered_at < '2025-12-02'
GROUP BY order_day
ORDER BY order_day;

Casting to date puts every order of the same calendar day in one group.

Fixing integer division starter

SELECT count(*) FILTER (WHERE status = 'cancelled') / count(*) AS wrong,
       round(count(*) FILTER (WHERE status = 'cancelled')::numeric / count(*), 4) AS cancel_rate
FROM orders;

bigint / bigint truncates to 0; casting one side gives the real rate.

Pitfalls

Casting a text column that contains one bad value: the whole query fails.
Assuming ::integer truncates (it rounds).
Casting a timestamp to date in WHERE on an indexed column (WHERE ordered_at::date = '2025-03-01') prevents a plain index on ordered_at from being used; prefer a range.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: No :: operator. CAST / CONVERT; TRY_CAST returns NULL instead of failing.
Snowflake: Supports :: and CAST; TRY_CAST returns NULL on failure.
BigQuery: CAST and SAFE_CAST (NULL on failure); no ::.
MySQL: CAST(x AS SIGNED / DECIMAL(10,2) / DATE / CHAR); target type names differ from PostgreSQL.