bar

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Number formatting. Draws a bar of block characters whose length shows where a number lies between a minimum and a maximum, padded with spaces to a fixed width. Use it to chart a column in a query result.

Syntax

bar(x, min, max)
bar(x, min, max, width)

Returns: VARCHAR.

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_numeric_bar. 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 six team scores out of 10. 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_numeric_bar"'

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}.scores"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, team VARCHAR, score DOUBLE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Amber', 2.5), "
    "(2, 'Blue', 5.0), "
    "(3, 'Coral', 7.6), "
    "(4, 'Dune', 10.0), "
    "(5, 'Echo', 12.0), "
    "(6, NULL, NULL)")

[]
[]
[(6,)]

What it does

bar(x, min, max, width) fills width × (x − min) / (max − min) of the width characters with the full block █ (U+2588) and pads the rest with spaces, so the result is always width characters long. A partial cell is drawn with a narrower block, in eighths of a cell rounded down: ▏ (U+258F) for one eighth, ▌ (U+258C) for half a cell.

A value outside the range is drawn at the nearest end: above max gives a full bar, below min an empty one.

NULLs and edge cases

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

run("SELECT bar(NULL, 0, 10, 20), bar(5, NULL, 10), bar(5, 0, NULL), bar(5, 0, 10, NULL)")
run("SELECT bar(15, 0, 10, 10), bar(-5, 0, 10, 10), bar(5, 10, 0, 10), bar(5, 0, 0, 10), bar(5, 5, 5, 10)")
run("SELECT bar(5, 0, 10, 1), bar(5, 0, 10, 1.5), length(bar(5, 0, 10, 1.5)), length(bar(5, 0, 10, 1000))")
run("SELECT bar('nan'::DOUBLE, 0, 10, 10), bar(5, 'nan'::DOUBLE, 10, 10), bar('inf'::DOUBLE, 0, 10, 10), bar(5, 0, 'inf'::DOUBLE, 10)")
run(f"SELECT bar('12', -5, 5, 20) FROM {TABLE} LIMIT 1")
run("SELECT bar(5, 0, 10, 0)")
run("SELECT bar(5, 0, 10, 1001)")
run(f"SELECT bar('abc', -5, 5, 20) FROM {TABLE} LIMIT 1")
run(f"SELECT bar(DATE '2026-10-16', -5, 5, 20) FROM {TABLE} LIMIT 1")

[(None, None, None, None)]
[('██████████', '          ', '          ', '██████████', '          ')]
[('▌', '▊', 1, 1000)]
[('          ', '          ', '██████████', '          ')]
[('████████████████████',)]
OSError: Max bar width must be >= 1
OSError: Max bar width must be <= 1000
OSError: Could not convert string 'abc' to DOUBLE
InternalError: Binder Error: No function matches the given name and argument types 'bar(DATE, INTEGER_LITERAL, INTEGER_LITERAL, INTEGER_LITERAL)'. You might need to add explicit type casts.

The calls in What it does:

run("SELECT bar(5, 0, 10, 20), bar(2.5, 0, 10, 20), bar(2.6, 0, 10, 20), bar(0, 0, 10, 20), bar(10, 0, 10, 20)")
run("SELECT length(bar(5, 0, 10)), length(bar(5, 0, 10, 20))")

[('██████████          ', '█████               ', '█████▏              ', '                    ', '████████████████████')]
[(80, 20)]

Examples

A bar chart of the scores, 20 characters for a score of 10. Echo's 12 is above the maximum and gets a full bar:

run(f"SELECT id, team, score, bar(score, 0, 10, 20) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'Amber', 2.5, '█████               '), (2, 'Blue', 5.0, '██████████          '), (3, 'Coral', 7.6, '███████████████▏    '), (4, 'Dune', 10.0, '████████████████████'), (5, 'Echo', 12.0, '████████████████████'), (6, None, None, None)]

Only the teams that scored 5 or more. The condition is on the raw column:

run(f"SELECT id, team, bar(score, 0, 10, 20) FROM {TABLE} WHERE score >= 5 ORDER BY id LIMIT 10")

[(2, 'Blue', '██████████          '), (3, 'Coral', '███████████████▏    '), (4, 'Dune', '████████████████████'), (5, 'Echo', '████████████████████')]

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

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

The plan of the chart query has no FILTER step: the scan reads score, and the Query Engine draws the bars:

plan(f"SELECT team, bar(score, 0, 10, 20) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│            team           │
│bar(score, 0.0, 10.0, 20.0)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            team           │
│           score           │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the raw column. score >= 5 is a comparison of a column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage, and bar() runs only on the rows that pass. Its plan has no FILTER step:

plan(f"SELECT team, bar(score, 0, 10, 20) FROM {TABLE} WHERE score >= 5")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│            team           │
│bar(score, 0.0, 10.0, 20.0)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           score           │
│            team           │
│                           │
└───────────────────────────┘

• Don't filter on the bar. A condition on bar() runs in a FILTER step; write the same condition on the number instead.

Limitations and errors

• Width from 1 to 1000. A width below 1 fails with Max bar width must be >= 1, and one above 1000 with Max bar width must be <= 1000.
• Numbers only. A date, a boolean, a BLOB or a list fails with No function matches the given name and argument types. A string is converted to DOUBLE, and fails if it isn't a number: Could not convert string 'abc' to DOUBLE.
• No error for an odd range. A reversed range, NaN or infinity gives an empty or a full bar, not an error. See NULLs and edge cases.

Similar functions

run("SELECT bar(25, 0, 100, 20), equi_width_bins(0, 100, 4, false)")

[('█████               ', [25, 50, 75, 100])]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• equi_width_bins.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.