Skip to content

type_conversion.md

Latest commit

 

History

History
116 lines (84 loc) · 3.57 KB

type_conversion.md

File metadata and controls

116 lines (84 loc) · 3.57 KB
Raw

Back to index

Type Conversion Functions and Operators

Function: CAST (Alias: CONVERT)

Description:
Converts a value to a specified SQL type. Fails if the conversion is invalid.

Inputs:

  • value (ANY type)
  • targetType — see the target-type list in Type Conversion Functions, which is the single authoritative list. It covers the character, numeric, temporal and binary families, the MySQL spellings SIGNED / UNSIGNED and DECIMAL / NUMERIC / DEC, and the parameterised forms (CHAR(10), DECIMAL(10,2), INT(11)) whose parameters are accepted and ignored.

Output:

  • targetType

Example:

SELECT CAST('2025-09-11' AS DATE) AS d FROM logs;
-- Result: 2025-09-11

Function: TRY_CAST (Alias: SAFE_CAST)

Description:
Attempts to convert a value to a specified SQL type. Returns NULL if the conversion fails instead of raising an error.

Inputs:

  • value (ANY type)
  • targetType (SQL type: INT, BIGINT, DOUBLE, DATE, DATETIME, etc.)

Output:

  • targetType (nullable)

Example:

SELECT TRY_CAST('invalid-date' AS DATE) AS d FROM logs;
-- Result: NULL

Operator: :: (Cast Operator)

Description:
Shorthand operator for casting. Equivalent to CAST(value AS type).

Inputs:

  • value (ANY type)
  • targetType (SQLType)

Output:

  • targetType

Example:

SELECT '2025-09-11'::DATE AS d, '125'::BIGINT AS b FROM logs;
-- Result: 2025-09-11, 125

Behavior Notes

  • CAST (CONVERT) will raise errors on invalid conversions.
  • TRY_CAST (SAFE_CAST) returns NULL instead of failing.
  • :: is syntactic sugar, easier to read in queries.
  • Type inference relies on baseType, and explicit CAST/TRY_CAST/:: updates the type context for following functions.

Restriction: cast the input of an aggregate, not its result

A cast cannot be applied to the result of an aggregate function. An aggregate has to be the first function in a column's chain, and a cast wrapping it would sit ahead of it:

-- Rejected: "Aggregation function must be the first function in the chain"
SELECT MAX(salary)::BIGINT AS m FROM emp GROUP BY dept;
SELECT CAST(MAX(salary) AS BIGINT) AS m FROM emp GROUP BY dept;

All four spellings behave the same way — ::, CAST, TRY_CAST and CONVERT.

Cast the aggregate's input instead. This is equivalent for MIN and MAX, whose result type follows their argument:

-- Both supported
SELECT MAX(salary::BIGINT) AS m FROM emp GROUP BY dept;
SELECT MAX(CAST(salary AS BIGINT)) AS m FROM emp GROUP BY dept;

COUNT, SUM, AVG, the STDDEV / VARIANCE family and the percentiles produce a type of their own (see Aggregate functions): COUNT is a BIGINT; AVG, the STDDEV / VARIANCE family and the percentiles are DOUBLE, whatever their input; SUM is a BIGINT over an input of any whole-number type and a DOUBLE over a REAL or a DOUBLE (since 0.25.0: it kept the summed column's type, which overflowed), so SUM(CAST(x AS INT)) is a BIGINT, not an INT. For these, casting the input converts each value before it is aggregated, which is not the same as converting the result.

Casting is unrestricted everywhere an aggregate is not involved — the SELECT list, WHERE, ORDER BY, and inside another function:

SELECT YEAR(createdAt)::VARCHAR AS y FROM logs;
SELECT id FROM logs WHERE tries::INT > 3 ORDER BY tries::INT;

Back to index