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:

   PreparedStatement pstmt = con.prepareStatement("select ... ");
   

2. Ask the driver for the result-set metadata of the prepared (unexecuted) statement:

   ResultSetMetaData meta = pstmt.getMetaData();
   

3. Iterate columns with a 1-based index (JDBC indexes start at 1, not 0):

   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:

   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:

   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:

   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:

   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:

   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:

   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.