--- title: "CAST and ::" slug: "dev-database-reference-sql-functions-conditional-and-cast-cast" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-cast" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # CAST and :: *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › Converting types.* `CAST(x AS type)` converts a value to another type, and `x::type` is a shorter way to write the same thing. A value that doesn't convert stops the query; to get NULL instead, use [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast). ## Syntax ``` CAST(x AS type) x::type type 'text' ``` | Argument | Type | Description | | --- | --- | --- | | `x` | any | The value to convert. | | `type` | a type name | The target type, such as `INTEGER`, `DECIMAL(10,2)`, `DATE` or `INTEGER[]` ([data types in SQL](/develop/docs/dev-database-reference-sql-data-types)). | **Returns:** a value of `type`. `NULL` converts to NULL of `type`. `type 'text'`, such as `DATE '2026-10-16'` or `INTEGER '42'`, is a typed literal: the same as `CAST('text' AS type)`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_conditional_and_cast_cast`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of five orders loaded from text. `qty` holds the quantity as text, and row 4 has a value that isn't a number. The last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_cast"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.orders" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, qty VARCHAR, units INTEGER, price DOUBLE, " "amount DECIMAL(10,2), shipped DATE, at TIMESTAMP)") run(f"INSERT INTO {TABLE} VALUES " "(1, '42', 3, 2.5, 2.50, DATE '2026-10-01', TIMESTAMP '2026-10-01 12:34:56.789'), " "(2, ' 12 ', 0, 3.5, 3.50, DATE '2026-10-02', TIMESTAMP '2026-10-02 00:00:00'), " "(3, '+5', -1, -2.5, -2.50, DATE '2026-10-03', TIMESTAMP '2026-10-03 23:59:59'), " "(4, 'ten', 7, 1.0, 1.00, DATE '2026-10-04', TIMESTAMP '2026-10-04 08:00:00'), " "(5, NULL, NULL, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does `CAST(x AS type)` and `x::type` return `x` as a value of `type`. They're the same operation: use whichever reads better. The main rules: - **Text to a number:** spaces, tabs and newlines around the number are ignored, and a leading `+` is allowed. Exponents (`'1e3'`), hexadecimal (`'0x1F'`), binary (`'0b101'`) and `_` between digits (`'1_000'`) are accepted. Thousands separators (`'1,000'`) and decimal commas (`'1,5'`) aren't. - **Rounding to an integer depends on the source type.** A `DECIMAL`, which is what a literal such as `2.5` is, and text such as `'2.5'` round half away from zero: 2.5 becomes 3, -2.5 becomes -3. A `DOUBLE` or `FLOAT` rounds half to even: 2.5 becomes 2, 3.5 becomes 4. - **Out of range is an error,** not a wrap-around: `300::TINYINT` fails with `Type INT32 with value 300 can't be cast because the value is out of range for the destination type INT8`. - **Text to `BOOLEAN`:** `'true'`, `'t'`, `'yes'`, `'y'` and `'1'` are true, `'false'`, `'f'`, `'no'` and `'0'` false (`'TRUE'` works too). Other text, such as `'on'`, fails, and so does text with spaces around it. A number is true unless it's 0. - **Text to `DATE`:** year, month and day in that order, separated by `-` or `/` (`'2026-10-16'`, `'2026/10/16'`, `'2026-1-6'`). A time after the date is dropped. Dates that don't exist, such as `'2026-02-29'`, fail. - **Text to `TIMESTAMP`:** `'2026-10-16 12:34:56'`, with `T` or a space between date and time, optional fractions of a second (kept to the microsecond) and an optional UTC offset. A value with an offset, such as `'+02'`, is converted to UTC. - **Text to a list:** `'[1, 2, 3]'::INTEGER[]`, with brackets. - **`VARCHAR(n)` doesn't truncate or check the length:** `'abc'::VARCHAR(1)` is `'abc'`. ### Precedence of :: `::` binds more tightly than arithmetic, and also more tightly than a leading minus sign. Put parentheses around an expression you want to cast: - `(1 + 1)::VARCHAR` is `'2'`, but `1 + 1::VARCHAR` adds a number to text and fails. - `-1::UTINYINT` is `-(1::UTINYINT)`, which gives `255`; `(-1)::UTINYINT` fails as out of range. - Casts chain from left to right: `'2.5'::DOUBLE::INTEGER` converts to `DOUBLE`, then rounds half to even, giving 2. ## NULLs and edge cases | Input | Result | | --- | --- | | `CAST(NULL AS INTEGER)`, `typeof(CAST(NULL AS INTEGER))` | `NULL`, `'INTEGER'` | | `' 42 '::INTEGER`, `'1e3'::INTEGER`, `'0x1F'::INTEGER`, `'1_000'::INTEGER` | `42`, `1000`, `31`, `1000` | | `2.5::INTEGER`, `2.5::DOUBLE::INTEGER`, `-2.5::DOUBLE::INTEGER` | `3`, `2`, `-2` | | `1.005::DECIMAL(10,2)`, `1.005::DOUBLE::DECIMAL(10,2)` | `1.01`, `1.00` (the `DOUBLE` is slightly below 1.005) | | `'yes'::BOOLEAN`, `'0'::BOOLEAN`, `2::BOOLEAN` | `true`, `false`, `true` | | `'2026/10/16'::DATE`, `'2026-10-16 12:00:00'::DATE` | `2026-10-16`, `2026-10-16` | | `'2026-10-16 12:34:56+02'::TIMESTAMP` | `2026-10-16 10:34:56` | | `'nan'::DOUBLE`, `'-Infinity'::DOUBLE` | `nan`, `-inf` | | `'[1,2,3]'::INTEGER[]`, `'abc'::VARCHAR(1)` | `[1, 2, 3]`, `'abc'` | | `(0.1::DOUBLE + 0.2::DOUBLE)::VARCHAR` | `'0.30000000000000004'` | | `''::INTEGER`, `'1,000'::INTEGER` | Error: `Could not convert string '' to INT32` (and the same for `'1,000'`) | | `300::TINYINT`, `'300'::TINYINT` | Error: out of range / `Could not convert string '300' to INT8` | | `' true '::BOOLEAN`, `'on'::BOOLEAN` | Error: `Could not convert string ' true ' to BOOL` | | `'20261016'::DATE`, `'2026-02-29'::DATE` | Error: `invalid date field format` / `date field value out of range` | | `'nan'::DOUBLE::INTEGER` | Error: out of range for `INT32` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-cast#set-up-the-examples): ``` run("SELECT CAST(NULL AS INTEGER), typeof(CAST(NULL AS INTEGER)), ' 42 '::INTEGER, '1e3'::INTEGER, " "'0x1F'::INTEGER, '1_000'::INTEGER") run("SELECT 2.5::INTEGER, 2.5::DOUBLE::INTEGER, -2.5::DOUBLE::INTEGER, 1.005::DECIMAL(10,2), " "1.005::DOUBLE::DECIMAL(10,2)") run("SELECT 'yes'::BOOLEAN, '0'::BOOLEAN, 2::BOOLEAN, '2026/10/16'::DATE, '2026-10-16 12:00:00'::DATE, " "'2026-10-16 12:34:56+02'::TIMESTAMP") run("SELECT 'nan'::DOUBLE, '-Infinity'::DOUBLE, '[1,2,3]'::INTEGER[], 'abc'::VARCHAR(1), " "(0.1::DOUBLE + 0.2::DOUBLE)::VARCHAR") for bad in ["''::INTEGER", "'1,000'::INTEGER", "300::TINYINT", "'300'::TINYINT", "' true '::BOOLEAN", "'on'::BOOLEAN", "'20261016'::DATE", "'2026-02-29'::DATE", "'nan'::DOUBLE::INTEGER"]: run(f"SELECT {bad}") ``` ``` [(None, 'INTEGER', 42, 1000, 31, 1000)] [(3, 2, -2, Decimal('1.01'), Decimal('1.00'))] [(True, False, True, datetime.date(2026, 10, 16), datetime.date(2026, 10, 16), datetime.datetime(2026, 10, 16, 10, 34, 56))] [(nan, -inf, [1, 2, 3], 'abc', '0.30000000000000004')] OSError: Could not convert string '' to INT32 OSError: Could not convert string '1,000' to INT32 OSError: Type INT32 with value 300 can't be cast because the value is out of range for the destination type INT8 OSError: Could not convert string '300' to INT8 OSError: Could not convert string ' true ' to BOOL OSError: Could not convert string 'on' to BOOL OSError: invalid date field format: "20261016", expected format is (YYYY-MM-DD) OSError: date field value out of range: "2026-02-29" OSError: Type DOUBLE with value nan can't be cast because the value is out of range for the destination type INT32 ``` The precedence cases: ``` run("SELECT (1 + 1)::VARCHAR, -1::UTINYINT, typeof(-1::UTINYINT), '2.5'::DOUBLE::INTEGER") run("SELECT 1 + 1::VARCHAR") run("SELECT (-1)::UTINYINT") ``` ``` [('2', 255, 'UTINYINT', 2)] InternalError: Binder Error: No function matches the given name and argument types '+(INTEGER_LITERAL, VARCHAR)'. You might need to add explicit type casts. OSError: Type INT32 with value -1 can't be cast because the value is out of range for the destination type UINT8 ``` ## Examples Convert the text quantities to integers. Spaces and a leading `+` are accepted: ``` run(f"SELECT id, qty, CAST(qty AS INTEGER), qty::INTEGER FROM {TABLE} WHERE id IN (1, 2, 3) ORDER BY id LIMIT 10") ``` ``` [(1, '42', 42, 42), (2, ' 12 ', 12, 12), (3, '+5', 5, 5)] ``` Round prices to integers: the `DOUBLE` column rounds half to even, the `DECIMAL` column half away from zero: ``` run(f"SELECT id, price, CAST(price AS INTEGER), amount, CAST(amount AS INTEGER) FROM {TABLE} " "WHERE id <= 3 ORDER BY id LIMIT 10") ``` ``` [(1, 2.5, 2, Decimal('2.50'), 3), (2, 3.5, 4, Decimal('3.50'), 4), (3, -2.5, -2, Decimal('-2.50'), -3)] ``` Convert between date, time and text types: ``` run(f"SELECT id, CAST(at AS DATE), at::TIME, CAST(at AS VARCHAR), shipped::TIMESTAMP FROM {TABLE} " "WHERE id <= 2 ORDER BY id LIMIT 10") ``` ``` [(1, datetime.date(2026, 10, 1), datetime.time(12, 34, 56, 789000), '2026-10-01 12:34:56.789', datetime.datetime(2026, 10, 1, 0, 0)), (2, datetime.date(2026, 10, 2), datetime.time(0, 0), '2026-10-02 00:00:00', datetime.datetime(2026, 10, 2, 0, 0))] ``` One value that doesn't convert stops the whole query, also when the cast is in `WHERE`. Use [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast) to get NULL for that row instead: ``` run(f"SELECT id, CAST(qty AS INTEGER) FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT count(*) FROM {TABLE} WHERE CAST(qty AS INTEGER) > 0") ``` ``` OSError: Could not convert string 'ten' to INT32 OSError: Could not convert string 'ten' to INT32 ``` Text in an `INSERT` is converted to the column's type, with the same rules; a value that doesn't convert stops the `INSERT`. The last statement removes the added row: ``` run(f"INSERT INTO {TABLE} (id, units, shipped) VALUES (6, '12', '2026-10-16')") run(f"SELECT id, units, shipped FROM {TABLE} WHERE id = 6") run(f"INSERT INTO {TABLE} (id, shipped) VALUES (7, '2026-02-30')") run(f"DELETE FROM {TABLE} WHERE id IN (6, 7)") ``` ``` [(1,)] [(6, 12, datetime.date(2026, 10, 16))] InternalError: Conversion Error: date field value out of range: "2026-02-30" [(1,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`), which evaluates the `WHERE` conditions it can push down. Where a cast runs depends on what it's applied to: - **A cast of a constant** is computed once, not for each row. `units = CAST('1' AS INTEGER)` and `units = '1'::INTEGER` are still comparisons of the column with a constant, and are pushed down. - **A widening cast of a column** keeps the condition pushed down: `CAST(units AS BIGINT) > 1` and `CAST(at AS DATE) = DATE '2026-10-01'` have no `FILTER` step. - **Other casts of a column in `WHERE`,** such as text to a number or a number to text, are a `FILTER` step in the Query Engine: the scan sends every row and the Query Engine converts each value. That is also why one bad value stops the query. - **In the `SELECT` list,** the Query Engine converts the values of the rows the scan returns. The plans of a widening cast and of a cast from text: ``` plan(f"SELECT id FROM {TABLE} WHERE CAST(units AS BIGINT) > 1") plan(f"SELECT id FROM {TABLE} WHERE CAST(qty AS INTEGER) > 5") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (CAST(qty AS INTEGER) > 5)│ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ qty │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Cast the constant, not the column.** `units = CAST('1' AS INTEGER)` is pushed down; `CAST(units AS VARCHAR) = '1'` is a `FILTER` in the Query Engine: ``` plan(f"SELECT id FROM {TABLE} WHERE units = CAST('1' AS INTEGER)") plan(f"SELECT id FROM {TABLE} WHERE CAST(units AS VARCHAR) = '1'") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (CAST(units AS VARCHAR) = │ │ '1') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ units │ │ id │ │ │ └───────────────────────────┘ ``` - **Store values in the type you filter on.** A number kept as text, like `qty`, is converted on every row of every query, and one bad value stops the query. Convert it once when you load the data. ## Limitations and errors - **Some conversions don't exist.** `DATE` or `TIMESTAMP` to a number and back, `TIME` to `TIMESTAMP`, a number to `INTERVAL`, and a `UUID` to anything but text fail with `Unimplemented type for cast`, followed by the two types. Use the [date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time) for dates and times. - **Text to `BLOB` takes ASCII only.** Non-ASCII text fails with `Invalid byte encountered in STRING -> BLOB conversion`; use [encode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-encode) for the UTF-8 bytes. In the other direction, `CAST(b AS VARCHAR)` shows bytes that aren't ASCII as `\xC3\xA9`-style escapes; use [decode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-decode) to read UTF-8. - **Dates in other orders don't convert.** `'16/10/2026'` and `'20261016'` fail with `invalid date field format`. Parse them with [strptime](/develop/docs/dev-database-reference-sql-functions-date-time-strptime) and a format. - **Time zone names don't convert to `TIMESTAMP`.** `'2026-10-16 12:34:56 Europe/Paris'::TIMESTAMP` fails with `... has a timestamp that is not UTC.` Use a UTC offset such as `+02`. - **Unknown type names fail before the query runs,** with `Catalog Error: Type with name NOSUCHTYPE does not exist!`. Other names are accepted for many types: `INT`, `INT4`, `SIGNED` (`INTEGER`), `INT8`, `LONG` (`BIGINT`), `INT2` (`SMALLINT`), `INT1` (`TINYINT`), `REAL`, `FLOAT4` (`FLOAT`), `FLOAT8`, `DOUBLE PRECISION` (`DOUBLE`), `NUMERIC` (`DECIMAL(18,3)`), `STRING`, `TEXT`, `CHAR`, `BPCHAR` (`VARCHAR`), `BOOL`, `LOGICAL` (`BOOLEAN`), `BYTEA`, `BINARY`, `VARBINARY` (`BLOB`) and `DATETIME` (`TIMESTAMP`). - **Implicit conversions are limited.** `'1' = 1` and `'a' || 1` work, but `1 + '1'` fails with `Could not choose a best candidate function`: cast one side. ``` run("SELECT DATE '2026-10-16'::INTEGER") run("SELECT '2026-10-16 12:34:56'::TIMESTAMP::BIGINT") run("SELECT '1'::NOSUCHTYPE") run("SELECT 1 + '1'") ``` ``` OSError: Unimplemented type for cast (DATE -> INTEGER) OSError: Unimplemented type for cast (TIMESTAMP -> BIGINT) InternalError: Catalog Error: Type with name NOSUCHTYPE does not exist! InternalError: Binder Error: Could not choose a best candidate function for the function call "+(INTEGER_LITERAL, STRING_LITERAL)". In order to select one, please add explicit type casts. ``` ## Similar functions | Expression | Gives for `'42'` | For text that isn't a number | | --- | --- | --- | | `CAST('42' AS INTEGER)`, `'42'::INTEGER` | `42` | an error that stops the query | | `INTEGER '42'` | `42` | an error | | [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast)`('42' AS INTEGER)` | `42` | `NULL` | | [typeof](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-typeof)`('42'::INTEGER)` | `'INTEGER'`: the type, not the value | | | [can_cast_implicitly](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-can-cast-implicitly)`(1::INTEGER, 1::BIGINT)` | `true`: whether one type converts to the other without a cast | | ``` run("SELECT CAST('42' AS INTEGER), '42'::INTEGER, INTEGER '42', TRY_CAST('42' AS INTEGER), TRY_CAST('x' AS INTEGER), " "typeof('42'::INTEGER), can_cast_implicitly(1::INTEGER, 1::BIGINT)") run("SELECT CAST('x' AS INTEGER)") ``` ``` [(42, 42, 42, 42, None, 'INTEGER', True)] OSError: Could not convert string 'x' to INT32 ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conversion functions. - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): type names and conversion rules. - [UUID values and casts](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-uuid-values): converting text to `UUID`. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.