---
title: "BIT values and casts"
slug: "dev-database-reference-sql-functions-binary-hash-uuid-bit-values"
updated: 2026-10-30T18:18:56Z
published: 2026-10-30T18:18:56Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-binary-hash-uuid-bit-values"
---

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

# BIT values and casts

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid) › Bit strings.* A `BIT` value is a string of 0s and 1s of any length, such as `'1011'::BIT`. You build one with a cast, from a `VARCHAR` of 0s and 1s or from an integer, and cast it to `VARCHAR` to read it. `BIT` isn't a table column type: store bit strings as `VARCHAR` and cast them in the query.

## Syntax

```
'bits'::BIT
CAST(value AS BIT)
TRY_CAST(value AS BIT)
CAST(bit_value AS type)
```

| Cast | From or to | Result |
| --- | --- | --- |
| To `BIT` | `VARCHAR` of 0s and 1s | the same bits |
| To `BIT` | `TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`, `HUGEINT` | the two's-complement bits: 8, 16, 32, 64 or 128 of them |
| To `BIT` | `DOUBLE` | the 64 bits of the floating-point value |
| To `BIT` | `BOOLEAN` | 8 bits: `true` is `'00000001'` |
| To `BIT` | `BLOB` | a `BIT` value built from the bytes |
| From `BIT` | `VARCHAR` | the 0s and 1s as text |
| From `BIT` | `INTEGER`, `BIGINT`, `BOOLEAN`, `BLOB` | the bits read as that type: `'101'::BIT` is `5` |

## 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_binary_hash_uuid_bit_values`. 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 feature-flag sets, stored as `VARCHAR` strings of 0s and 1s. The third 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_binary_hash_uuid_bit_values"'

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}.flags"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, bits VARCHAR, mask VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '1011', '1100'), "
    "(2, '0', '1'), "
    "(3, NULL, NULL), "
    "(4, '10000000', '11111111'), "
    "(5, '0110', '0011')")
```

```
[]
[]
[(5,)]
```

## What it does

A cast to `BIT` reads its input as a sequence of bits:

- **A `VARCHAR`** must hold only the characters `0` and `1`. Spaces, a `0b` prefix or any other character fail the cast, and so does an empty string. `TRY_CAST` returns `NULL` instead.
- **An integer** gives its two's-complement bits, as many as the type has: `CAST(42 AS BIT)` is 32 bits (`42` is an `INTEGER`), `CAST(42::TINYINT AS BIT)` is `&apos;00101010&apos;`.
- **A `DOUBLE`** gives the 64 bits of its floating-point form, and **a `BOOLEAN`** gives 8 bits.

The client receives a `BIT` value as bytes in an internal encoding (`&apos;1011&apos;::BIT` arrives as `b&apos;\x04\xfb&apos;`), so cast it to `VARCHAR` to read it, as the examples on this page do. Casting back the other way, `&apos;101&apos;::BIT` is `5` as an `INTEGER` or `BIGINT`, and `&apos;1&apos;::BIT` is `true` as a `BOOLEAN`.

Two `BIT` values are equal when they have the same bits and the same length: `&apos;1011&apos;::BIT = &apos;01011&apos;::BIT` is false. A string literal compared with a `BIT` value is cast to `BIT`.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `CAST(NULL AS BIT)` | `NULL` |
| `&apos;&apos;::BIT` | Error: `Cannot cast empty string to BIT` |
| `&apos;1021&apos;::BIT`, `&apos; 1011&apos;::BIT`, `&apos;0b1011&apos;::BIT` | Error: `Invalid character encountered in string -&gt; bit conversion: &apos;2&apos;` (the first bad character) |
| `TRY_CAST(&apos;1021&apos; AS BIT)`, `TRY_CAST(&apos;&apos; AS BIT)` | `NULL` |
| `CAST(CAST(-1::SMALLINT AS BIT) AS VARCHAR)` | `&apos;1111111111111111&apos;` |
| `CAST(CAST(true AS BIT) AS VARCHAR)` | `&apos;00000001&apos;` |
| `&apos;1011&apos;::BIT = &apos;01011&apos;::BIT` | `false` |
| A bit string of 100,000 bits | works |

The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-bit-values#set-up-the-examples):

```
run("SELECT CAST(NULL AS BIT), TRY_CAST('1021' AS BIT), TRY_CAST('' AS BIT)")
run("SELECT ''::BIT")
run("SELECT '1021'::BIT")
run("SELECT ' 1011'::BIT")
run("SELECT CAST(CAST(-1::SMALLINT AS BIT) AS VARCHAR), CAST(CAST(true AS BIT) AS VARCHAR), "
    "'1011'::BIT = '01011'::BIT, length(CAST(repeat('10', 50000) AS BIT))")
```

```
[(None, None, None)]
OSError: Cannot cast empty string to BIT
OSError: Invalid character encountered in string -> bit conversion: '2'
OSError: Invalid character encountered in string -> bit conversion: ' '
[('1111111111111111', '00000001', False, 100000)]
```

## Examples

Cast each stored string to `BIT`, and show what the client receives with and without a cast back to `VARCHAR`:

```
run(f"SELECT id, CAST(bits AS BIT), CAST(CAST(bits AS BIT) AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, b'\x04\xfb', '1011'), (2, b'\x07\xfe', '0'), (3, None, None), (4, b'\x00\x80', '10000000'), (5, b'\x04\xf6', '0110')]
```

The bits of integers of each size, of a `DOUBLE` and of a `BOOLEAN`:

```
run("SELECT CAST(CAST(42::TINYINT AS BIT) AS VARCHAR), CAST(CAST(42 AS BIT) AS VARCHAR), "
    "CAST(CAST(2.5::DOUBLE AS BIT) AS VARCHAR)")
```

```
[('00101010', '00000000000000000000000000101010', '0100000000000100000000000000000000000000000000000000000000000000')]
```

Read bits back as numbers, as a boolean and as bytes:

```
run("SELECT CAST('101'::BIT AS INTEGER), CAST('00000000000000000000000000101010'::BIT AS INTEGER), "
    "CAST('101'::BIT AS BIGINT), CAST('1'::BIT AS BOOLEAN), CAST('101'::BIT AS BLOB)")
```

```
[(5, 42, 5, True, b'\x05')]
```

Compare bit strings, and join two of them with `||`. The result of `||` is a `VARCHAR`, not a `BIT` value:

```
run("SELECT '1011'::BIT = '1011'::BIT, '1011'::BIT = '1011', '1011'::BIT < '1100'::BIT, "
    "CAST('1011' AS BIT) || CAST('01' AS BIT), typeof(CAST('1011' AS BIT) || CAST('01' AS BIT))")
```

```
[(True, True, True, '101101', 'VARCHAR')]
```

## 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`). The table holds `VARCHAR` strings, so a cast to `BIT` is always computed by the Query Engine:

- **In the `SELECT` list,** the scan reads the column and the Query Engine casts it for each row it returns.
- **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine casts it and keeps the rows that match. No rows are skipped in storage.

The plan of a filter on the cast shows the `FILTER` step above the scan:

```
plan(f"SELECT id FROM {TABLE} WHERE CAST(bits AS BIT) = '1011'::BIT")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (CAST(bits AS BIT) = 1011)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            bits           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Compare the stored string.** `bits = &apos;1011&apos;` compares the raw `VARCHAR` column with a constant, so it's pushed down to VAST DataBase and rows that don't match never leave storage. It finds the same rows as `CAST(bits AS BIT) = &apos;1011&apos;::BIT` as long as the strings are stored without leading spaces or other characters. The plan has no `FILTER` step:

```
plan(f"SELECT id FROM {TABLE} WHERE bits = '1011'")
run(f"SELECT id FROM {TABLE} WHERE bits = '1011' ORDER BY id LIMIT 10")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(1,)]
```

- **Cast only the rows you need.** Combine the cast with a condition on a raw column, such as `id &gt; 100`: that condition is still evaluated in VAST DataBase, so the cast runs only on the rows that pass it.

## Limitations and errors

- **No `BIT` columns.** `CREATE TABLE` with a `BIT` or `BIT[]` column, and `CREATE TABLE ... AS SELECT` of a `BIT` value, fail with `BIT type is not supported in table definitions`. Store a `VARCHAR` of 0s and 1s, or an integer, and cast it in the query.

```
run(f"CREATE TABLE {SCHEMA}.bits_tbl (id BIGINT, b BIT)")
```

```
InternalError: Binder Error: BIT type is not supported in table definitions. Column "b" has type: BIT
```

- **One bad string fails the query.** `CAST(col AS BIT)` on a column with a value that isn't 0s and 1s fails with `Invalid character encountered in string -&gt; bit conversion`. Use `TRY_CAST(col AS BIT)` to get `NULL` for those rows.
- **The client receives bytes.** Without a cast to `VARCHAR`, a `BIT` value arrives as bytes in an internal encoding. Cast it to `VARCHAR` before you return it.
- **Sorting doesn't follow text order.** `ORDER BY CAST(bits AS BIT)` and `&lt;` between bit strings of different lengths follow the internal encoding, not the order of the text: `&apos;011&apos;::BIT &lt; &apos;1&apos;::BIT` is true, and so is `&apos;10000000&apos;::BIT &lt; &apos;0&apos;::BIT`. To sort in text order, sort by the `VARCHAR` column.

```
run(f"SELECT id, bits FROM {TABLE} WHERE bits IS NOT NULL ORDER BY CAST(bits AS BIT) LIMIT 10")
run("SELECT '011'::BIT < '1'::BIT, '10000000'::BIT < '0'::BIT")
```

```
[(4, '10000000'), (5, '0110'), (1, '1011'), (2, '0')]
[(True, True)]
```

## Similar functions

| Function | What it gives for 5 | Result |
| --- | --- | --- |
| `CAST(&apos;101&apos; AS BIT)` | the bits you write | `&apos;101&apos;` |
| [bitstring](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-bitstring) | bits padded on the left to a length | `bitstring(&apos;101&apos;, 8)` is `&apos;00000101&apos;` |
| `CAST(5 AS BIT)` | all 32 bits of an `INTEGER` | `&apos;00000000000000000000000000000101&apos;` |
| [set_bit](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-set-bit) | a copy with one bit changed | `set_bit(&apos;000&apos;::BIT, 0, 1)` is `&apos;100&apos;` |
| [bin](/develop/docs/dev-database-reference-sql-functions-numeric-bin) | a `VARCHAR` of the binary digits, without leading zeros | `&apos;101&apos;` |

```
run("SELECT CAST('101'::BIT AS VARCHAR), CAST(bitstring('101', 8) AS VARCHAR), CAST(CAST(5 AS BIT) AS VARCHAR), "
    "CAST(set_bit('000'::BIT, 0, 1) AS VARCHAR), bin(5)")
```

```
[('101', '00000101', '00000000000000000000000000000101', '100', '101')]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [Binary, hash and UUID functions](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid#bit-strings): all the bit string functions.
- [CAST and ::](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-cast) and [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast).
- [Bitwise operators on BIT values](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-bitwise-operators-on-bit).
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).