# Postgres Column Metadata And Type Mapping

> PostgreSQL Column Metadata & Client-Side Type Mapping

- Skill: `mikerrr/postgres-column-metadata-and-type-mapping` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mikerrr/postgres-column-metadata-and-type-mapping`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mikerrr/postgres-column-metadata-and-type-mapping/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: mikerrr (https://skillmd.com/u/mikerrr)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/mikerrr/postgres-column-metadata-and-type-mapping

---


# PostgreSQL Column Metadata & Client-Side Type Mapping

I extract the shape (column names + data types) of PostgreSQL queries and tables from client drivers, and I control how PostgreSQL types materialise as native objects in the client language.

## Prerequisites I confirm before acting

1. A reachable PostgreSQL server and valid connection parameters: `host`, `port`, `user`, `password`, `db_name`, and — for schema inspection — the target table name (schema-qualified if it is not in the default search path).
2. Sufficient privileges: at minimum SELECT/USAGE on the target tables and schemas, so the statement can be prepared/described and catalog metadata can be read.
3. For Python work:
   - Python 3 environment.
   - `psycopg2` (or `psycopg2-binary`) installed — it serves both as the raw driver and as the SQLAlchemy DBAPI.
   - `sqlalchemy` installed (provides `create_engine`, `inspect`, `text`).
   - Optionally `tqdm` for progress bars when iterating over many tables/columns.
4. For JVM work: the PostgreSQL JDBC driver on the classpath and an open `java.sql.Connection` (`con`).
5. The SQL statement whose result-set metadata is wanted, written so that it can be *prepared*: valid syntax, resolvable identifiers. Parameter placeholders are fine — the statement is only prepared, never executed.
6. Access to PostgreSQL type OIDs (e.g. `date` = 1082) from the `pg_type` catalog, if type casting is to be overridden.
7. The SQLAlchemy URL pattern: `postgresql+psycopg2://{user}:{password}@{host}:{port}/{db_name}` — with special characters in the password URL-encoded.

## Instructions

### Step 1: Classify the metadata need

I decide which of two distinct paths applies:

- **(a) Arbitrary query, must not be executed** → prepared-statement metadata (describe). This is the only path that describes computed expressions, aliases, joins and function results.
- **(b) Existing stored table** → catalog/schema inspection via the SQLAlchemy `Inspector`. This is authoritative for stored columns, nullability and defaults, but it cannot describe query output shape.

If both are needed (e.g. build a mapping layer), I run both paths and normalise the results (see Step 5).

### Step 2: Query column names and types WITHOUT executing the query (JDBC / any JVM language)

1. Prepare, do not execute:
   ```java
   PreparedStatement pstmt = con.prepareStatement("select ... ");
   ```
2. Ask the driver for the result-set metadata of the prepared (unexecuted) statement:
   ```java
   ResultSetMetaData meta = pstmt.getMetaData();
   ```
3. Iterate columns with a **1-based** index (JDBC indexes start at 1, not 0):
   ```java
   for (int i = 1; i <= meta.getColumnCount(); i++) {
       System.out.println("Column name: " + meta.getColumnName(i)
                        + ", data type: " + meta.getColumnTypeName(i));
   }
   ```
   - `meta.getColumnCount()` → number of output columns.
   - `meta.getColumnName(i)` → output column name/alias.
   - `meta.getColumnTypeName(i)` → PostgreSQL data type name as reported by the driver.
4. Close/release the prepared statement afterwards. The query itself was never executed: no rows were fetched, no side effects occurred, nothing was modified.

### Step 3: Column names and types of a table via SQLAlchemy inspection (Python)

1. Import what is needed:
   ```python
   from sqlalchemy import create_engine, inspect, text
   import psycopg2
   from tqdm import tqdm
   ```
2. Write a reusable function that builds the engine, creates an inspector and returns the column list:
   ```python
   def get_columns_and_types(table, host, port, user, password, db_name):
       engine = create_engine(
           f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{db_name}"
       )
       inspector = inspect(engine)
       columns = inspector.get_columns(table)
       return columns
   ```
3. Interpret the return value: `inspector.get_columns(table)` returns a **list of dictionaries**, one per column, each containing at least the column `name` and its `type` (a SQLAlchemy type object), plus attributes such as nullability and default when available.
4. Read the name/type pairs:
   ```python
   for col in get_columns_and_types("my_table", host, port, user, password, db_name):
       print(col["name"], col["type"])
   ```
5. When inspecting many tables, I wrap the iteration in `tqdm(...)` for progress, and I **reuse a single `engine`/`inspector`** instead of creating a new engine per table (a per-table engine spawns a new connection pool every call).
6. I use `text("...")` from SQLAlchemy whenever a raw SQL statement must go through the same engine — e.g. to query `pg_type` for OIDs, or to run a `LIMIT 0` probe.
7. If the table lives outside the default schema, I pass the schema explicitly to the inspector rather than relying on an unqualified name.

### Step 4: Control how PostgreSQL types convert into Python objects (psycopg2 custom casting)

I apply this when the default mapping is not what is wanted — e.g. psycopg2 returns `datetime.date(2012, 8, 5)` but the plain string `'2012-08-05'` is required.

1. Find the OID of the SQL type to override. Example: `date_oid = 1082` (the id of the `date` type). I read OIDs from the `pg_type` catalog whenever I am not certain.
2. Define a casting function with the required two-argument signature `(val, cur)`, where `val` is the raw string value returned by the server (or `None` for SQL NULL) and `cur` is the cursor:
   ```python
   date_oid = 1082  # id of date type; look it up in the DB if unknown

   def casting_fn(val, cur):
       # process as you like, e.g. string formatting
       return val
   ```
   I handle `val is None` explicitly so the caster never raises on NULLs.
3. Build a new type object binding the OID tuple, a human-readable type name and the casting function:
   ```python
   datetype_casted = psycopg2.extensions.new_type((date_oid,), "date", casting_fn)
   ```
   The first argument is a **tuple** of OIDs — the trailing comma for a single OID is mandatory.
4. Register the new type so psycopg2 uses it:
   ```python
   psycopg2.extensions.register_type(datetype_casted)
   ```
5. Verify the effect. A row that previously came back as
   `[('sample title', 'sample body', datetime.date(2012, 8, 5))]`
   now comes back as
   `[('sample title', 'sample body', '2012-08-05')]`.
6. I always prefer this explicit type-casting customisation over hacks such as changing the client encoding to influence value formatting.
7. I scope the registration deliberately: globally when the whole process should share the behaviour, or by passing a specific connection/cursor as the scope argument to `register_type` when only one place should be affected.

### Step 5: Combine the pieces into a full metadata pipeline

1. For ad-hoc SQL of unknown shape: prepare/describe the statement to obtain output column names and type names without executing it.
2. For persisted tables: inspect the catalog to get authoritative column names, types, nullability and defaults.
3. Map the discovered PostgreSQL type names/OIDs to the Python (or target-language) representation required, registering custom casters where the default conversion is unsuitable.
4. Cache or reuse the engine/connection and dispose of it when done, to avoid connection leaks.

## Success criteria I verify

- Every output column of an arbitrary SQL statement is listed, in order, with its name and PostgreSQL type name, without the statement ever being executed (no rows fetched, no side effects, no data modified).
- The JDBC loop runs from `i = 1` to `meta.getColumnCount()` **inclusive** and prints exactly one `Column name: ..., data type: ...` line per output column.
- `get_columns_and_types(...)` returns a non-empty list of dicts for an existing table, each dict exposing the column `name` and `type`, matching the actual table definition.
- The SQLAlchemy URL `postgresql+psycopg2://user:password@host:port/db_name` connects successfully and the inspector reads metadata without errors.
- After registering a custom type with `psycopg2.extensions.new_type` + `register_type`, query results contain the desired Python representation (e.g. the `'2012-08-05'` string instead of `datetime.date(2012, 8, 5)`), and no other column types are unintentionally altered.
- The whole procedure is repeatable across many tables/queries without connection leaks or per-call engine proliferation.

## Troubleshooting

**`getMetaData()` returns null / throws, or metadata is unavailable.**
Metadata is only available if the server could successfully *prepare* the statement. I check that the SQL is syntactically valid and every identifier resolves, and that I called `getMetaData()` on a `PreparedStatement` (not a plain `Statement`). I also never execute the query merely to learn its shape — that is unnecessary and potentially expensive or side-effecting.

**The last column is missing, or `getColumnName(0)` fails.**
JDBC metadata indexes are 1-based. `i < getColumnCount()` silently drops the final column and index `0` is invalid. I use `for (int i = 1; i <= meta.getColumnCount(); i++)`.

**`inspector.get_columns(table)` returns an empty list.**
The table is almost certainly in a non-default schema. I pass the schema explicitly to the inspector (or schema-qualify the name) instead of relying on the search path.

**Connection string parsing breaks.**
Special characters (`@`, `:`, `/`, `#`) in the password must be URL-encoded inside the SQLAlchemy URL.

**Custom type registration has no effect, or raises.**
Three usual causes: (1) the OID tuple was passed as a bare integer — `new_type((date_oid,), ...)` needs the trailing comma; (2) the OID is wrong — `1082` is `date`; `timestamp`, `timestamptz`, `numeric` and array types all have different OIDs, and array types have their own OIDs distinct from their element types, so I look them up in `pg_type` rather than guessing; (3) the casting function has the wrong signature — it must accept exactly `(val, cur)` and tolerate `val is None`.

**Unrelated code changed behaviour after registration.**
I registered globally when only one connection needed the override. I re-register with a specific connection/cursor as the scope argument.

**Types from the two paths do not match.**
SQLAlchemy inspector returns SQLAlchemy *type objects*; JDBC `getColumnTypeName` returns driver-reported PostgreSQL *type name strings*. I normalise both sides before comparing. And I never expect catalog inspection to describe computed or aliased query expressions — for query output shape I use the prepared-statement describe path.

**Connection pool exhaustion / too many connections.**
A naive per-table helper creates a new `Engine` (and therefore a new pool) on every call. I hoist the engine out of the loop, reuse it, and `engine.dispose()` when finished.