pg_typeof

Applies to VAST Cluster 5.5.

Functions › Session and utility functions › Types. Returns the SQL type of its argument as lower-case text, such as bigint, varchar or decimal(10,2). It's the lower-case form of typeof; pg_catalog.pg_typeof is the same function.

Syntax

pg_typeof(expression)
pg_catalog.pg_typeof(expression)

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_utility_pg_typeof. 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 two items with columns of ten types. The second 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_utility_pg_typeof"'

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}.items"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, price DECIMAL(10,2), score DOUBLE, "
    "tags VARCHAR[], attrs STRUCT(a INTEGER, b VARCHAR), added DATE, seen TIMESTAMP, ok BOOLEAN, raw BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'lamp', 2.50, 0.5, ['home'], {'a': 1, 'b': 'x'}, DATE '2026-10-16', "
    "TIMESTAMP '2026-10-16 12:34:56', true, '\\xAA'::BLOB), "
    "(2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(2,)]

What it does

pg_typeof names the type the Query Engine gives its argument, in lower case:

The type depends only on the expression, not on the value: a NULL in a BIGINT column is still bigint.

run(f"SELECT id, pg_typeof(id), pg_typeof(price), pg_typeof(tags), pg_typeof(attrs) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT pg_typeof(42), pg_typeof(1.5), pg_typeof(1e300), pg_typeof(1 + 1.5), pg_typeof(now())")
run("SELECT pg_typeof(INTERVAL 1 DAY), pg_typeof([1, NULL, 3]), pg_typeof([[1, 2], [3]]), "
    "pg_typeof(MAP {'k': 'v'}), pg_typeof('{}'::JSON)")

[(1, 'bigint', 'decimal(10,2)', 'varchar[]', 'struct(a integer, b varchar)'), (2, 'bigint', 'decimal(10,2)', 'varchar[]', 'struct(a integer, b varchar)')]
[('integer', 'decimal(2,1)', 'double', 'decimal(12,1)', 'timestamp with time zone')]
[('interval', 'integer[]', 'integer[][]', 'map(varchar, varchar)', 'json')]

NULLs and edge cases

run("SELECT pg_typeof(NULL), pg_typeof([]), pg_typeof({'a': NULL}), pg_typeof(NULL::BIGINT)")
run("SELECT pg_typeof('NaN'::DOUBLE), pg_catalog.pg_typeof('abc')")

[('"null"', '"null"[]', 'struct(a "null")', 'bigint')]
[('double', 'varchar')]

Examples

The type of every column of the table, read from one row:

run(f"SELECT pg_typeof(name), pg_typeof(score), pg_typeof(added), pg_typeof(seen), pg_typeof(ok), "
    f"pg_typeof(raw) FROM {TABLE} WHERE id = 1")

[('varchar', 'double', 'date', 'timestamp', 'boolean', 'blob')]

Check what type an expression produces before you store it, for example the result of a division:

run("SELECT pg_typeof(7 / 2), pg_typeof(7 // 2), pg_typeof(2 ** 70)")

[('double', 'integer', 'double')]

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 type of a column or an expression is fixed when the query is planned, so pg_typeof doesn't need the rows: in WHERE, a condition such as pg_typeof(price) = 'decimal(10,2)' is settled while the query is planned. The plan has no FILTER step, and the condition keeps every row or none:

plan(f"SELECT id FROM {TABLE} WHERE pg_typeof(price) = 'decimal(10,2)'")
run(f"SELECT id FROM {TABLE} WHERE pg_typeof(price) = 'decimal(10,2)' ORDER BY id LIMIT 10")

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

Make it faster

• Read the types of a table with DESCRIBE. It lists every column and its type:

run(f"DESCRIBE {TABLE}")

[('id', 'BIGINT', 'YES', None, None, None), ('name', 'VARCHAR', 'YES', None, None, None), ('price', 'DECIMAL(10,2)', 'YES', None, None, None), ('score', 'DOUBLE', 'YES', None, None, None), ('tags', 'VARCHAR[]', 'YES', None, None, None), ('attrs', 'STRUCT(a INTEGER, b VARCHAR)', 'YES', None, None, None), ('added', 'DATE', 'YES', None, None, None), ('seen', 'TIMESTAMP', 'YES', None, None, None), ('ok', 'BOOLEAN', 'YES', None, None, None), ('raw', 'BLOB', 'YES', None, None, None)]

• Add LIMIT 1 when you read types from a table with pg_typeof: every row has the same answer.

Limitations and errors

• A NULL without a type is named "null", with the double quotes; so is the element type of an empty list. Cast the value to give it a type.
• The names are lower case. Compare them with lower-case text, such as 'bigint', or use typeof for the upper-case names.

Similar functions

run("SELECT pg_typeof(1), typeof(1), pg_typeof('a'), typeof('a'), pg_typeof([1]), typeof([1]), "
    "pg_typeof(NULL), typeof(NULL)")

[('integer', 'INTEGER', 'varchar', 'VARCHAR', 'integer[]', 'INTEGER[]', '"null"', '"NULL"')]

Clean up:

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

[]
[]

See also

• typeof.
• Functions: all the function families.
• EXPLAIN: how to read a plan.