printf

Applies to VAST Cluster 5.5.

Functions › Text basics › Formatting. Builds a string from a C-style template, replacing each % conversion, such as %s or %05d, with the next value. printf has no aliases; sprintf isn't available.

Syntax

printf(template, value, ...)

Returns: VARCHAR.

Aliases: none.

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_printf. 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 orders. 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_printf"'

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, customer VARCHAR, qty BIGINT, amount DECIMAL(10, 2), ordered DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Ada Lovelace', 3, 1234.50, DATE '2026-10-01'), "
    "(2, 'Grace Hopper', 12, 89.99, DATE '2026-10-02'), "
    "(3, 'José Martí', 1, 15.00, DATE '2026-10-05'), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

printf copies template and replaces each conversion with the next value, from left to right. A conversion is %, optional flags, an optional width, an optional precision after a ., and a letter that says how to write the value. %% is a literal %.

Conversions

A width is a minimum: %-8.3e on 2.5 gives 2.500e+00, nine characters, with no padding.

Which values each conversion accepts

%s accepts strings, dates, booleans, lists and BLOB values, but not numbers. Write integers with %d and decimals with %f or %e. The number conversions don't mix: %f on an integer such as 42 fails with Invalid type specifier "f" for formatting a value of type int, and %d on a decimal such as 1.5 with Invalid type specifier "d" for formatting a value of type float:

%d on a string fails the same way: Invalid type specifier "d" for formatting a value of type string.

NULLs and edge cases

A NULL among several values also makes the whole result NULL: printf('%s %s', 'a', NULL) returns NULL.

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

run("SELECT printf(NULL), printf('%s', NULL), printf('[%s]', ''), printf('[%s]', DATE '2026-10-16'), printf('[%s]', true), printf('[%s]', ['a', 'b']), printf('[%s]', '\\xAA'::BLOB)")
run("SELECT printf('%s %s', 'a')")
run("SELECT printf('[%s]', 123)")
run("SELECT printf('[%s]', 1.5)")
run("SELECT printf('%d', 'x')")

[(None, None, '[]', '[2026-10-16]', '[true]', '[[a, b]]', '[\\xAA]')]
OSError: Argument index "1" out of range
OSError: Invalid type specifier "s" for formatting a value of type int
OSError: Invalid type specifier "s" for formatting a value of type float
OSError: Invalid type specifier "d" for formatting a value of type string

The conversions from the table above:

run("SELECT printf('%-6s|%5s|', 'ab', 'cd'), printf('%.3s', 'abcdef'), printf('%05d', 42), printf('%,d', 1234567)")
run("SELECT printf('%.2f', 3.14159), printf('%5.1f|%-8.3e', 3.14159, 2.5), printf('%e', 12345.678)")
run("SELECT printf('%x %X %o', 255, 255, 8), printf('%c', 65), printf('%%')")

[('ab    |   cd|', 'abc', '00042', '1,234,567')]
[('3.14', '  3.1|2.500e+00', '1.234568e+04')]
[('ff FF 10', 'A', '%')]

Examples

A sentence for each order: %s for the name and the date, %d for the quantity. The row with no values gives NULL:

run(f"SELECT id, printf('%s ordered %d items on %s', customer, qty, ordered) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Ada Lovelace ordered 3 items on 2026-10-01'), (2, 'Grace Hopper ordered 12 items on 2026-10-02'), (3, 'José Martí ordered 1 items on 2026-10-05'), (4, None)]

Aligned columns for a fixed-width report: the name left-aligned in 14 characters, the quantity right-aligned in 4, and the amount with two decimals, right-aligned in 10:

run(f"SELECT id, printf('%-14s|%4d|%10.2f', customer, qty, amount) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Ada Lovelace  |   3|   1234.50'), (2, 'Grace Hopper  |  12|     89.99'), (3, 'José Martí    |   1|     15.00'), (4, None)]

Find an order by its display code, an id padded with zeros to five digits:

run(f"SELECT id, customer FROM {TABLE} WHERE printf('ORD-%05d', id) = 'ORD-00002' ORDER BY id LIMIT 10")

[(2, 'Grace Hopper')]

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). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so printf() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine computes printf() for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes printf() and keeps the rows that match. No rows are skipped in storage.

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

plan(f"SELECT id FROM {TABLE} WHERE printf('ORD-%05d', id) = 'ORD-00002'")

┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│ (printf('ORD-%05d', id) = │
│        'ORD-00002')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the raw value, and format only for display. printf('ORD-%05d', id) = 'ORD-00002' keeps the same row as id = 2, but only the second is a comparison of a raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id = 2")

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

• Combine it with a pushed-down condition. In id > 100 AND printf('%s', customer) = '...', id > 100 is still evaluated in VAST DataBase, so printf() runs only on the rows that pass it.

Limitations and errors

• %s doesn't accept numbers. An integer fails with Invalid type specifier "s" for formatting a value of type int, and a decimal with ... of type float. A DOUBLE, such as 2.5::DOUBLE, fails the same way. Use %d for integers and %f for decimals, cast the number to VARCHAR, or use format, whose {} accepts any type.
• The value must suit the conversion. A string for %d fails with Invalid type specifier "d" for formatting a value of type string. %d doesn't accept decimals (... of type float) and %f doesn't accept integers (... of type int). Use the conversion that matches the value's type, or format with a plain {}.
• One value per conversion. Fewer values than conversions fails with Argument index "1" out of range: values count from 0, so index 1 is the second value.
• A NULL value makes the whole result NULL. Replace it first with coalesce.
• sprintf isn't available. It fails with Scalar Function with name sprintf does not exist!. Use printf.

run("SELECT printf('%d', 123), printf('%s', CAST(123 AS VARCHAR))")
run("SELECT sprintf('%s', 'a')")

[('123', '123')]
InternalError: Catalog Error: Scalar Function with name sprintf does not exist!

Similar functions

run("SELECT printf('%05d|%.2f|%s', 42, 3.14159, 'x'), format('{:05d}|{:.2f}|{}', 42, 3.14159, 'x')")
run("SELECT printf('%s', NULL), format('{}', NULL), concat('a', NULL, 'b'), 'a' || NULL || 'b', concat_ws('-', 'a', NULL, 'b')")

[('00042|3.14|x', '00042|3.14|x')]
[(None, None, 'ab', None, 'a-b')]

Clean up:

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

[]
[]

See also

• Text basics: all the text functions.
• format: the same job with {} placeholders.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.