--- title: "map_to_pg_oid" slug: "dev-database-reference-sql-functions-maps-map-to-pg-oid" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-maps-map-to-pg-oid" --- > ## 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. # map_to_pg_oid *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) › PostgreSQL compatibility.* Returns the PostgreSQL type OID (object identifier) for a type name. It's a helper of the `pg_catalog` schema, and it knows few names: to find the type of a value or a column, use `typeof()` or [DESCRIBE](/develop/docs/dev-database-reference-sql-describe). `pg_catalog.map_to_pg_oid` is the same function. ## Syntax ``` map_to_pg_oid(type_name) pg_catalog.map_to_pg_oid(type_name) ``` | Argument | Type | Description | | --- | --- | --- | | `type_name` | `VARCHAR` | A type name, as the PostgreSQL catalog spells it. | **Returns:** `INTEGER`. **Aliases:** `pg_catalog.map_to_pg_oid`. ## 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_maps_map_to_pg_oid`. 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 five type names. The last row has no name: ``` 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_maps_map_to_pg_oid"' 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, "") 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}.type_names" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR)") run(f"INSERT INTO {TABLE} VALUES (1, 'bool'), (2, 'BOOLEAN'), (3, 'int4'), (4, 'VARCHAR'), (5, NULL)") ``` ``` [] [] [(5,)] ``` ## What it does `map_to_pg_oid` returns the OID that the PostgreSQL catalog gives a type name, or NULL if it doesn't know the name: - **`'bool'` returns 16.** The other names tried return NULL: `'BOOLEAN'`, `'int4'`, `'INTEGER'`, `'BIGINT'`, `'VARCHAR'`, `'text'`, `'DATE'`, `'TIMESTAMP'`, `'DOUBLE'` and `'MAP'`. - **Names are compared exactly.** The plan in [How it runs in VAST](/develop/docs/dev-database-reference-sql-functions-maps-map-to-pg-oid#how-it-runs-in-vast) shows the function as a `CASE` expression that compares the name with a fixed list of lower-case names, such as `'bool'`, `'int'`, `'bigint'` and `'date'`, so `'BIGINT'` and `'DATE'` don't match. - **It doesn't work on maps.** The name comes from the PostgreSQL catalog, not from the `MAP` type. ## NULLs and edge cases | Input | Result | | --- | --- | | `map_to_pg_oid(NULL)` | `NULL` | | `map_to_pg_oid('bool')`, `pg_catalog.map_to_pg_oid('bool')` | `16` | | `map_to_pg_oid('BOOLEAN')`, `map_to_pg_oid('int4')`, `map_to_pg_oid('text')` | `NULL` | | `map_to_pg_oid('nope')`, `map_to_pg_oid('')` | `NULL` | | `map_to_pg_oid(123)` (a number) | Error: `Conversion Error: Could not convert string 'bool' to INT32` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-maps-map-to-pg-oid#set-up-the-examples): ``` run("SELECT map_to_pg_oid(NULL), map_to_pg_oid('bool'), pg_catalog.map_to_pg_oid('bool'), typeof(map_to_pg_oid('bool'))") run("SELECT map_to_pg_oid('BOOLEAN'), map_to_pg_oid('int4'), map_to_pg_oid('text'), map_to_pg_oid('nope'), " "map_to_pg_oid('')") run("SELECT map_to_pg_oid(123)") ``` ``` [(None, 16, 16, 'INTEGER')] [(None, None, None, None, None)] InternalError: Conversion Error: Could not convert string 'bool' to INT32 ``` ## Examples The OID of each name in the table: ``` run(f"SELECT id, name, map_to_pg_oid(name) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'bool', 16), (2, 'BOOLEAN', None), (3, 'int4', None), (4, 'VARCHAR', None), (5, None, None)] ``` ## 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 scan reads the `name` column, and the Query Engine computes `map_to_pg_oid()` for each row it returns. The plan shows the scan and the step above it, which holds the whole lookup: ``` plan(f"SELECT map_to_pg_oid(name) FROM {TABLE}") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ CASE WHEN ((name = 'bool'│ │ )) THEN (16) WHEN ((name =│ │ 'int16')) THEN (21) WHEN │ │ ((name = 'int')) THEN (23)│ │ WHEN ((name = 'bigint')) │ │ THEN (20) WHEN ((name = │ │ 'date')) THEN (1082) WHEN │ │ ((name = 'time')) THEN │ │ (1083) WHEN ((name = │ │ 'datetime')) THEN (1114) │ │ WHEN ((name = 'dec')) │ │ THEN (1700) WHEN ((name =│ │ 'float')) THEN (700) WHEN│ │ ((name = 'double')) THEN │ │ (701) WHEN ((name = │ │ 'bpchar')) THEN (1043) │ │ WHEN ((name = 'binary')) │ │ THEN (17) WHEN ((name = │ │ 'interval')) THEN (1186) │ │ WHEN ((name = │ │ 'timestamptz')) THEN (1184│ │ ) WHEN ((name = 'timetz'))│ │ THEN (1266) WHEN ((name =│ │ 'bit')) THEN (1560) WHEN │ │ ((name = 'guid')) THEN │ │ (2950) ELSE NULL END │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: name │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Use `typeof()` or `DESCRIBE` to find a type.** `typeof(value)` returns the type name of any value in the query, and [DESCRIBE](/develop/docs/dev-database-reference-sql-describe) lists the type of every column of a table, without a lookup that returns NULL for most names. See [Similar functions](/develop/docs/dev-database-reference-sql-functions-maps-map-to-pg-oid#similar-functions). - **Look a name up once.** If you need an OID, look it up in a query of its own (`SELECT map_to_pg_oid('bool')`) rather than for every row of a table. ## Limitations and errors - **Few names are known.** The plan lists the names it compares with, and the match is exact. Don't build on it to map types; use `typeof()` or `DESCRIBE`. - **The argument must be a string.** A number fails with `Could not convert string 'bool' to INT32`, and a list with `Type VARCHAR with value 'bool' can't be cast to the destination type LIST`: the errors name `'bool'`, not your value. ``` run("SELECT map_to_pg_oid(['bool'])") ``` ``` InternalError: Conversion Error: Type VARCHAR with value 'bool' can't be cast to the destination type LIST ``` ## Similar functions | Function | Returns | Result for a `BOOLEAN` value or column | | --- | --- | --- | | `map_to_pg_oid('bool')` | the PostgreSQL OID of a PostgreSQL type name | `16` | | `typeof(value)` | the type name of a value, as the VAST Query Engine names it | `'BOOLEAN'` | | [DESCRIBE](/develop/docs/dev-database-reference-sql-describe) | the name and type of each column of a table | `BOOLEAN` in the type column | ``` run("SELECT map_to_pg_oid('bool'), typeof(true), typeof(1), typeof('a')") run(f"DESCRIBE {TABLE}") ``` ``` [(16, 'BOOLEAN', 'INTEGER', 'VARCHAR')] [('id', 'BIGINT', 'YES', None, None, None), ('name', 'VARCHAR', 'YES', None, None, None)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): the map functions (`map_to_pg_oid` isn't one of them). - [Data types in SQL](/develop/docs/dev-database-reference-sql-data-types): the type names the VAST Query Engine uses. - [DESCRIBE](/develop/docs/dev-database-reference-sql-describe): the columns and types of a table.