md5

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Hashes. Returns the MD5 digest of a string or of a BLOB, as a VARCHAR of 32 lower-case hexadecimal digits. For the same digest as a number, see md5_number.

Syntax

md5(x)

Returns: VARCHAR (32 characters).

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_binary_hash_uuid_md5. 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 four documents, each with a name and its first bytes. 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_binary_hash_uuid_md5"'

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}.documents"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, data BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'hello', '\\x25\\x50\\x44\\x46'::BLOB), "
    "(2, '', ''::BLOB), "
    "(3, 'a', '\\x61'::BLOB), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

md5 computes the 128-bit MD5 digest of its argument and writes it as 32 hexadecimal digits, in lower case: md5('abc') is '900150983cd24fb0d6963f7d28e17f72'.

A VARCHAR is hashed as its UTF-8 bytes, so a string and the same bytes as a BLOB have the same digest: md5('hello'), md5('hello'::BLOB) and md5(encode('hello')) are equal. The digest depends on every byte: md5('Hello') and md5('hello') differ.

Use it for checksums, change detection and keys built from content. MD5 isn't suitable for passwords or signatures.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT md5(NULL), md5(''), md5(''::BLOB), length(md5('a'))")
run("SELECT md5('hello') = md5('hello'::BLOB), md5('hello') = md5(encode('hello')), upper(md5('abc')) = md5('abc')")
run("SELECT md5(123)")
run("SELECT md5(CAST(42 AS VARCHAR))")
run("SELECT md5('a', 'b')")

[(None, 'd41d8cd98f00b204e9800998ecf8427e', 'd41d8cd98f00b204e9800998ecf8427e', 32)]
[(True, True, False)]
InternalError: Binder Error: No function matches the given name and argument types 'md5(INTEGER_LITERAL)'. You might need to add explicit type casts.
[('a1d0c6e83f027327d8461063f4ac58a6',)]
InternalError: Binder Error: No function matches the given name and argument types 'md5(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

The digest of each document's name and of its bytes:

run(f"SELECT id, md5(name), md5(data) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '5d41402abc4b2a76b9719d911017c592', 'bfa4b10a76324b166cfdad5e02a63730'), (2, 'd41d8cd98f00b204e9800998ecf8427e', 'd41d8cd98f00b204e9800998ecf8427e'), (3, '0cc175b9c0f1b6a831c399e269772661', '0cc175b9c0f1b6a831c399e269772661'), (4, None, None)]

Find a document by the digest of its name:

run(f"SELECT id, name FROM {TABLE} WHERE md5(name) = '5d41402abc4b2a76b9719d911017c592' ORDER BY id LIMIT 10")

[(1, 'hello')]

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). md5() is computed by the Query Engine:

• On a column in the SELECT list, the scan reads the column and the Query Engine computes the digest for each row it returns.
• On a column in WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine hashes each value and keeps the rows that match. No rows are skipped in storage.
• On a constant, such as name = md5('x'), the Query Engine computes the digest once, before the scan, and the comparison of the column with it is pushed down to VAST DataBase.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE md5(name) = '5d41402abc4b2a76b9719d911017c592'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│       (md5(name) =        │
│'5d41402abc4b2a76b9719d9110│
│          17c592')         │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the digest if you look rows up by it. Compute md5() when you write the rows and keep it in its own VARCHAR column. A lookup is then a comparison of a column with a constant, which VAST DataBase evaluates: the plan for a comparison with md5('x') has no FILTER step, because the digest of the constant is computed once:

plan(f"SELECT id FROM {TABLE} WHERE name = md5('x')")

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

Limitations and errors

• Strings and bytes only. A number, a DECIMAL, a date, a boolean, a UUID or a BIT value fails with No function matches the given name and argument types, and the error lists md5(VARCHAR) and md5(BLOB). Cast the value to VARCHAR first: md5(CAST(42 AS VARCHAR)), md5(CAST(u AS VARCHAR)) for a UUID column.
• Lower case only. Compare the result with a lower-case digest, or apply lower() to the other side; hex writes capitals.
• Other names aren't available. md5_hex and sha512 fail with Scalar Function with name ... does not exist!. Use md5, or sha1 and sha256.

run("SELECT md5(DATE '2026-10-16')")
run("SELECT md5_hex('a')")
run("SELECT sha512('a')")

InternalError: Binder Error: No function matches the given name and argument types 'md5(DATE)'. You might need to add explicit type casts.
InternalError: Catalog Error: Scalar Function with name md5_hex does not exist!
InternalError: Catalog Error: Scalar Function with name sha512 does not exist!

Similar functions

run("SELECT md5('hello'), md5_number('hello'), md5_number_upper('hello'), md5_number_lower('hello')")
run("SELECT sha1('hello'), sha256('hello'), typeof(hash('hello'))")

[('5d41402abc4b2a76b9719d911017c592', Decimal('-145191729247072235933599665601768308387'), 8514701317032132957, 10575884659879408057)]
[('aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824', 'UBIGINT')]

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: all the functions in this family.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.