---
title: "BLOB values, comparisons and ||"
slug: "dev-database-reference-sql-functions-binary-hash-uuid-blob-values"
updated: 2026-10-30T18:18:51Z
published: 2026-10-30T18:18:51Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-binary-hash-uuid-blob-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.

# BLOB values, comparisons and ||

*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) › Binary data and encodings.* A `BLOB` is a string of bytes. This page shows how to write `BLOB` constants, how casts to and from `BLOB` behave, how `BLOB` values compare, and how `||` joins them.

## Syntax

```
'\x25\x50\x44\x46'::BLOB
BLOB '\xAA'
CAST(s AS BLOB)
CAST(b AS VARCHAR)
a = b    a <> b    a < b    a <= b    a > b    a >= b
a BETWEEN low AND high
a IN (b1, b2, ...)
a || b
```

| Argument | Type | Description |
| --- | --- | --- |
| `s` | `VARCHAR` | Text of ASCII characters and `\x` escapes, to turn into bytes. |
| `a`, `b` | `BLOB` | The values to compare or join. |

**Returns:** `BOOLEAN` for comparisons; `BLOB` for `a || b` when both sides are `BLOB`.

## 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_blob_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 six rows with two `BLOB` columns: file headers and trailers. Row 2 has empty bytes, row 3 has NULLs, row 4 holds the UTF-8 bytes of `h&#xe9;llo`, and row 5 bytes that aren't text:

```
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_blob_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}.chunks"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, head BLOB, tail BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '\\x25\\x50\\x44\\x46'::BLOB, '\\x68\\x69'::BLOB), "
    "(2, ''::BLOB, ''::BLOB), "
    "(3, NULL, NULL), "
    "(4, encode('h\u00e9llo'), '\\x00\\x7F'::BLOB), "
    "(5, '\\x00\\x01'::BLOB, '\\xFF\\xFE'::BLOB), "
    "(6, '\\x61'::BLOB, '\\x61'::BLOB)")
```

```
[]
[]
[(6,)]
```

## What it does

**Writing a constant.** Cast a string to `BLOB`. Each ASCII character stands for its own byte, and `\x` followed by two hex digits, in either case, stands for any byte: `&apos;\x41\x42&apos;::BLOB`, `&apos;AB&apos;::BLOB` and `&apos;\x41B&apos;::BLOB` are the same two bytes, and `&apos;\xaa&apos;::BLOB = &apos;\xAA&apos;::BLOB` is true. `BLOB &apos;\xAA&apos;` is the same as `&apos;\xAA&apos;::BLOB`. A NUL byte is `\x00`: `&apos;a\x00b&apos;::BLOB` is three bytes. A character outside ASCII must be written as its bytes, such as `\xC3\xA9` for é; to get the UTF-8 bytes of any text, use [encode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-encode). The client receives `BLOB` values as Python `bytes`.

**Casts.** `CAST(s AS BLOB)` turns ASCII text into its bytes, and inserting a string into a `BLOB` column does the same. `CAST(b AS VARCHAR)` writes each byte that isn't printable ASCII as a `\x` escape in capitals: `CAST(&apos;\xFF\x41&apos;::BLOB AS VARCHAR)` is `&apos;\xFFA&apos;`. That includes UTF-8 text: the bytes of `h&#xe9;llo` cast to `&apos;h\xC3\xA9llo&apos;`. To read bytes as UTF-8 text, use [decode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-decode).

**Comparisons.** `BLOB` values compare byte by byte, each byte as a number from 0 to 255; when one value is a prefix of the other, the shorter comes first. So `&apos;&apos;::BLOB &lt; &apos;\x00&apos;::BLOB`, `&apos;ab&apos;::BLOB &lt; &apos;b&apos;::BLOB` and `&apos;\xFF&apos;::BLOB &gt; &apos;a&apos;::BLOB` are all true. A string constant on the other side is cast to `BLOB`: `&apos;ab&apos;::BLOB = &apos;ab&apos;` is true. `BETWEEN`, `IN` and `ORDER BY` use the same order.

**Joining.** `a || b` joins two `BLOB` values into one. If either side is NULL, the result is NULL.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `CAST(NULL AS BLOB)` | `NULL` |
| `&apos;ab&apos;::BLOB \|\| NULL` | `NULL` |
| `&apos;&apos;::BLOB` | `b&apos;&apos;`, empty bytes |
| `&apos;a\x00b&apos;::BLOB` | `b&apos;a\x00b&apos;`, three bytes |
| `&apos;&apos;::BLOB &lt; &apos;\x00&apos;::BLOB` | `true` |
| `&apos;\xFF&apos;::BLOB &gt; &apos;a&apos;::BLOB` | `true` |
| `&apos;ab&apos;::BLOB \|\| &apos;cd&apos;` (a string on one side) | `&apos;abcd&apos;`, a `VARCHAR`, not a `BLOB` |
| `&apos;\x4&apos;::BLOB` | Error: `Conversion Error: Invalid hex escape code encountered in string -&gt; blob conversion of string &quot;\\x4&quot;: unterminated escape code at end of blob` |
| `&apos;&#xe9;&apos;::BLOB` | Error: `Conversion Error: Invalid byte encountered in STRING -&gt; BLOB conversion of string &quot;&#xe9;&quot;. All non-ascii characters must be escaped with hex codes (e.g. \\xAA)` |
| `CAST(42 AS BLOB)` | Error: `Unimplemented type for cast (INTEGER -&gt; BLOB)` |

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

```
run("SELECT CAST(NULL AS BLOB), 'ab'::BLOB || NULL, ''::BLOB, 'a\\x00b'::BLOB, octet_length('a\\x00b'::BLOB)")
run("SELECT ''::BLOB < '\\x00'::BLOB, '\\xFF'::BLOB > 'a'::BLOB, 'ab'::BLOB < 'b'::BLOB, '\\xaa'::BLOB = '\\xAA'::BLOB")
run("SELECT 'ab'::BLOB || 'cd', typeof('ab'::BLOB || 'cd'), 'ab'::BLOB || 'cd'::BLOB")
run("SELECT CAST('\\xFF\\x41'::BLOB AS VARCHAR), BLOB '\\xAA'")
run("SELECT '\\x4'::BLOB")
run("SELECT '\u00e9'::BLOB")
run("SELECT CAST(42 AS BLOB)")
```

```
[(None, None, b'', b'a\x00b', 3)]
[(True, True, True, True)]
[('abcd', 'VARCHAR', b'abcd')]
[('\\xFFA', b'\xaa')]
InternalError: Conversion Error: Invalid hex escape code encountered in string -> blob conversion of string "\\x4": unterminated escape code at end of blob
InternalError: Conversion Error: Invalid byte encountered in STRING -> BLOB conversion of string "é". All non-ascii characters must be escaped with hex codes (e.g. \\xAA)
OSError: Unimplemented type for cast (INTEGER -> BLOB)
```

## Examples

Compare and join the two columns of each row. Row 3 has NULLs, so every result is NULL:

```
run(f"SELECT id, head < tail, head = tail, head || tail FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, True, False, b'%PDFhi'), (2, False, True, b''), (3, None, None, None), (4, False, False, b'h\xc3\xa9llo\x00\x7f'), (5, True, False, b'\x00\x01\xff\xfe'), (6, False, True, b'aa')]
```

Sort by the bytes. Empty bytes come first, and the NULL comes last:

```
run(f"SELECT id, head FROM {TABLE} ORDER BY head LIMIT 10")
```

```
[(2, b''), (5, b'\x00\x01'), (1, b'%PDF'), (6, b'a'), (4, b'h\xc3\xa9llo'), (3, None)]
```

Find the rows that start with a PDF header, and show the bytes as text. The cast writes the bytes of row 4 as `\x` escapes:

```
run(f"SELECT id FROM {TABLE} WHERE head = '\\x25\\x50\\x44\\x46'::BLOB ORDER BY id LIMIT 10")
run(f"SELECT id, CAST(head AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1,)]
[(1, '%PDF'), (2, ''), (3, None), (4, 'h\\xC3\\xA9llo'), (5, '\\x00\\x01'), (6, 'a')]
```

## 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`). What runs where depends on the condition:

- **A comparison of a `BLOB` column with a constant** (`=`, `&gt;=` and the other comparisons) and `IS NULL` are pushed down to VAST DataBase: rows that don't match never leave storage, and the plan has no `FILTER` step.
- **`IN` with a list of `BLOB` constants** isn't pushed down: the scan sends every row and a `FILTER` step in the Query Engine tests it.
- **`||`, casts and functions of a column** are computed in the Query Engine, after the scan.

The plan of the equality test has no `FILTER` step; the plan of the `IN` test has one:

```
plan(f"SELECT id FROM {TABLE} WHERE head = '\\x25\\x50\\x44\\x46'::BLOB")
plan(f"SELECT id FROM {TABLE} WHERE head IN ('\\x61'::BLOB, ''::BLOB)")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  ((head = 'a'::BLOB) OR   │
│     (head = ''::BLOB))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            head           │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Compare the stored column with a constant.** `head = &apos;\x25\x50\x44\x46&apos;::BLOB` is pushed down; `decode(head) = &apos;%PDF&apos;` makes the Query Engine test every row.
- **Build the constant with a function if that's easier.** A function of a constant is computed once, so `head = from_hex(&apos;25504446&apos;)`, `head = from_base64(&apos;JVBERg==&apos;)` and `head = encode(&apos;a&apos;)` are still pushed down:

```
plan(f"SELECT id FROM {TABLE} WHERE head = from_hex('25504446')")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

## Limitations and errors

- **Text functions don't take a `BLOB`.** `length()`, `substring()`, `upper()` and `LIKE` fail with `No function matches the given name and argument types`. Use [octet_length](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-octet-length) for the size, or [decode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-decode) the bytes to text first.
- **Few casts.** A `BLOB` casts to and from `VARCHAR` and `BIT` ([BIT values and casts](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-bit-values)). `CAST(42 AS BLOB)`, `CAST(b AS INTEGER)` and `CAST(b AS UUID)` fail with `Unimplemented type for cast`.
- **Non-ASCII text in a constant.** A cast of text with a character outside ASCII fails with `Invalid byte encountered in STRING -&gt; BLOB conversion`, and so does `CAST(col AS BLOB)` on a column that holds such text. Use [encode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-encode).
- **A string on one side of `||` gives a string.** `&apos;ab&apos;::BLOB || &apos;cd&apos;` and `concat(&apos;ab&apos;::BLOB, &apos;cd&apos;::BLOB)` return a `VARCHAR`. Cast the string side to `BLOB` to keep bytes.
- **`min()` and `max()` of a `BLOB` column aren't available.** They fail with `The operator &apos;UNGROUPED_AGGREGATE&apos; type=8 is currently not supported for this usage`. Use `ORDER BY col LIMIT 1`.
- **`ORDER BY` needs a `LIMIT`.** Without one, the query fails with `The operator &apos;ORDER_BY&apos; type=1 is currently not supported for this usage`.

```
run("SELECT length('hello'::BLOB)")
run("SELECT 'ab'::BLOB LIKE 'a%'")
run(f"SELECT min(head) FROM {TABLE}")
```

```
InternalError: Binder Error: No function matches the given name and argument types 'length(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types '~~(BLOB, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
```

## Similar functions

| Function | Result for the bytes of `h&#xe9;` (`h`, then `&#xe9;` as two bytes) |
| --- | --- |
| `CAST(b AS VARCHAR)` | `&apos;h\xC3\xA9&apos;`: bytes outside printable ASCII as escapes |
| [decode](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-decode) | `&apos;h&#xe9;&apos;`: the bytes read as UTF-8 |
| [hex](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-hex) | `&apos;68C3A9&apos;` |
| [to_base64](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-to-base64) | `&apos;aMOp&apos;` |
| [octet_length](/develop/docs/dev-database-reference-sql-functions-binary-hash-uuid-octet-length) | `3`: the number of bytes |

```
run("SELECT CAST(b AS VARCHAR), decode(b), hex(b), to_base64(b), octet_length(b) "
    "FROM (SELECT encode('h\u00e9') AS b)")
```

```
[('h\\xC3\\xA9', 'hé', '68C3A9', 'aMOp', 3)]
```

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#binary-data-and-encodings): all the binary functions.
- [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): `BLOB` columns.
- [The concatenation operator](/develop/docs/dev-database-reference-sql-functions-string-concat-operator) on strings, lists and other types.
- [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.