Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

CAST and ::

Prev Next

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › 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.

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).

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), 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), 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, "<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:

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 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 for dates and times.
  • Text to BLOB takes ASCII only. Non-ASCII text fails with Invalid byte encountered in STRING -> BLOB conversion; use 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 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 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('42' AS INTEGER) 42 NULL
typeof('42'::INTEGER) 'INTEGER': the type, not the value
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

Related articles
  • TRY_CAST
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Conditional and conversion functions
  • can_cast_implicitly
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Conditional and conversion functions
  • // (integer division)
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Operators