Type Conversion — CAST, TRY_CAST, CONVERT, ::

Four spellings convert a value to another SQL type. They differ in failure behaviour and in argument order, not in capability.

Form	Syntax	On invalid input
CAST	CAST(expr AS TYPE)	raises an error
CONVERT	CONVERT(expr, TYPE) or CONVERT(TYPE, expr)	raises an error
TRY_CAST (alias SAFE_CAST)	TRY_CAST(expr AS TYPE)	returns NULL
:: operator	expr::TYPE	raises an error

CONVERT accepts both argument orders — CONVERT(expr, TYPE) and the Transact-SQL CONVERT(TYPE, expr). SAFE_CAST is accepted as an alias and normalises to TRY_CAST.

SELECT CAST(hired_on AS DATE)         AS d FROM emp;
SELECT emp_no::BIGINT                 AS b FROM emp;
SELECT TRY_CAST(hired_on AS DATE)     AS d FROM emp;  -- NULL where unparseable
SELECT CONVERT(salary, DOUBLE)        AS s FROM emp;

Without a FROM clause, a SELECT of constant expressions is accepted — the connection check BI tools send — and a constant cast works there in every spelling: CAST('125' AS BIGINT), CONVERT('125', BIGINT) and '125'::BIGINT. See FROM-less SELECT.

Target types

Category	Accepted target types
String	VARCHAR, STRING, CHAR, TEXT, KEYWORD
Integer	TINYINT, SMALLINT, INT, INTEGER, BIGINT, SIGNED, UNSIGNED
Floating point	DOUBLE, FLOAT, REAL, DECIMAL, NUMERIC, DEC
Boolean	BOOLEAN
Temporal	DATE, TIME, DATETIME, TIMESTAMP
Binary	BINARY, VARBINARY

A length, precision or scale may be written on any type SQL parameterises — CHAR(10), VARCHAR(255), DECIMAL(10,2), INT(11), TIMESTAMP(3) — and is accepted and ignored.

Three limitations

• SIGNED and UNSIGNED are both 64-bit signed integers (BIGINT). Elasticsearch’s unsigned_long is not addressable from a cast, so a value above Long.MAX_VALUE is not representable.
• DECIMAL / NUMERIC / DEC are approximate: they map to DOUBLE. Elasticsearch has no exact decimal type.
• A precision or length is ignored — no rounding, no truncation. The statement re-renders without it (CAST(x AS DECIMAL(10,2)) becomes CAST(x AS DOUBLE), CHAR(10) becomes CHAR), so every artifact that echoes a statement — SHOW CREATE TABLE, a materialized view’s .sql, a log line — shows the type the engine actually applied.

BOOL is not a cast target; write BOOLEAN. BLOB, CLOB, BIT, MONEY, UUID and INTERVAL as a type are rejected.

CONVERT(expr USING <charset>) is accepted as a synonym for CONVERT(expr, VARCHAR): Elasticsearch stores UTF-8 throughout, so the charset is parsed and dropped.

Cast the input of an aggregate, not its result

A cast cannot be applied to the result of an aggregate. An aggregate must 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 here. Cast the aggregate’s input instead — equivalent for every aggregate whose result type follows its argument (MIN, MAX, SUM, AVG, the STDDEV / VARIANCE family, the percentiles):

-- 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;

The same applies in HAVING: HAVING COUNT(*)::INT > 3 is rejected, and unnecessary — compare against a numeric literal directly.

Where casts are allowed

Anywhere 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;

Temporal casts

In a query (since 0.24.0):

• A date-only string cast to TIMESTAMP or DATETIME is its midnight UTC: CAST('2024-01-03' AS TIMESTAMP) is 2024-01-03T00:00:00Z, over a KEYWORD column too.
• A DATE value cast to BIGINT is the epoch milliseconds of its midnight UTC, and cast to TIME is 00:00:00: this holds for a CAST(… AS DATE), CURRENT_DATE, a DATE literal, LAST_DAY and a CASE / COALESCE / NULLIF / GREATEST / LEAST that answers a DATE. A DATE column answers its stored instant: CAST(d AS BIGINT) is that instant’s epoch milliseconds, and CAST(d AS TIME) its time of day in UTC.
• A temporal expression cast to TIME, DATE or BIGINT converts its value: CAST(DATE_TRUNC(ts, HOUR) AS TIME) is the time of day, AS DATE the day, AS BIGINT the epoch milliseconds.
• A number cast to DATE is refused, as PostgreSQL refuses it ((d - d2)::DATE); a number cast to TIMESTAMP is read as epoch milliseconds, as above.
• CAST(NULL AS VARCHAR) is NULL, not the text 'null'.

DuckDB refuses a DATE cast to BIGINT or to TIME; the values above are this engine’s rule.

In a computed column

CREATE TABLE orders (
  id INT,
  zip_code KEYWORD,
  zip_n BIGINT SCRIPT AS (TRY_CAST(zip_code AS BIGINT))
);

'75001' stores zip_n = 75001. 'N/A' is still indexed, with zip_n set to NULL: the _source carries "zip_n": null and no doc value is written, so that row is skipped by WHERE zip_n IS NOT NULL and by any aggregation over zip_n. A failed safe cast is a NULL operand for whatever wraps it, so UPPER(…), LENGTH(…), ABS(…) and ROUND(…) over it are NULL for that row, while CONCAT(TRY_CAST(zip_code AS BIGINT), '-X') is '-X', because CONCAT skips a NULL argument, as DuckDB does.

NULLIF over a cast works in a computed column, a projection and a WHERE alike — NULLIF(TRY_CAST(zip_code AS BIGINT), 0) and NULLIF(CAST(zip_code AS BIGINT), 0). The one shape still refused is comparing a value that really is text with a number (NULLIF(status, 0)); the engine names the two types and asks you to cast one of them.

Before 0.24.0

A safe cast in a computed column produced a malformed script, so the CREATE TABLE itself failed, and NULLIF over any cast was rejected with a script compile error — the comparison was decided by the column’s declared type rather than by what the cast renders.

A cast decides the arithmetic around it

An operand’s type is what it renders, not what its column is declared as. A cast therefore changes the arithmetic that follows it, in both directions:

-- price DOUBLE, value 5.0
SELECT CAST(price AS INTEGER) / 2 AS c FROM t;   -- 2   (both operands are integers)
SELECT price / 2 AS c FROM t;                    -- 2.5 (unchanged)
SELECT CAST(qty AS DOUBLE) / 2 AS c FROM t;      -- 2.5 for an INTEGER qty

Before 0.24.0 the target type came from the column, with two consequences: a narrowing cast did not affect the division, and — the reason this changed — a cast of a text column was ignored entirely, so CAST(amount_str AS BIGINT) + fee concatenated and '125' + 7 stored 1257 instead of 132.

CAST(5.0 AS INTEGER) / 2 is 2 in PostgreSQL, and 2.5 in MySQL and DuckDB, which treat / as always-decimal and spell truncation DIV and //. We follow PostgreSQL. For the fractional result, cast to a floating type or divide by a decimal literal (/ 2.0) — both say what you mean on every engine.

Behaviour notes

• A number cast to TIMESTAMP is read as epoch milliseconds, UTC — CAST(1704902400000 AS TIMESTAMP) is 2024-01-10 12:00:00. The same holds over an integer or decimal column (CAST(created_ms AS TIMESTAMP)), in a SELECT, in a WHERE and in a computed column; a fraction of a millisecond is dropped, toward zero.

• A cast is not needed to avoid integer division: since 0.24.0 / always yields a floating-point result, whatever its operands are. A cast still decides what the operand is — CAST(price AS INTEGER) / 2 over 7.5 divides 7 and answers 3.5 — but never makes the division integral; write (a / b)::INTEGER for a truncated quotient.

• CAST and CONVERT raise an error on an invalid conversion; TRY_CAST returns NULL, which makes it the right choice for parsing user-supplied or loosely-typed source fields.

• An explicit cast updates the type context for functions applied after it, so '125'::BIGINT + 1 is arithmetic rather than string concatenation. Since 0.24.0 that holds for a column too — see below.

• Casting is evaluated inside the generated Painless script, so a cast on a field participates in script-based filtering and sorting like any other expression.