PG_TYPEOF

Return the PG-compat data type name of an expression.

Category: miscReturns: STRINGDialect: PostgreSql

Syntax

PG_TYPEOF(expr)

Description

## Overview Returns the PG-compat name of the data type of the input expression. Use this function for diagnostics, schema exploration, and to confirm type inference in generated SQL. PG_TYPEOF is the PG-compat variant of TYPEOF. TYPEOF returns dialect-neutral type names (for example, `string`), while PG_TYPEOF returns PG-compat names (for example, `text`). ## Behavior - Always returns a non-NULL string; even for a NULL argument the function reports the inferred type of the expression. - Deterministic for a given expression tree. - Side effect free. - The type is resolved at plan time from the expression's inferred type, not the runtime value. ## Compatibility - PG-compat alias. Type names match `pg_typeof` output where the types are equivalent.

Parameters

Name	Type	Description
`expr`		Specifies the expression whose data type name is returned.

Examples

-- Type of an integer literal
SELECT PG_TYPEOF(42);  -- 'integer'

-- Type of a string literal
SELECT PG_TYPEOF('hello');  -- 'text'

-- Type of a computed expression
SELECT PG_TYPEOF(1.5 + 2);  -- 'numeric'

-- Inspect column types in a sample row
SELECT PG_TYPEOF(created_at) AS ts_type, PG_TYPEOF(amount) AS amt_type
FROM sales.catalog.orders
LIMIT 1;

-- Use in a diagnostic JOIN to verify type alignment
SELECT PG_TYPEOF(a.id) AS a_type, PG_TYPEOF(b.ref_id) AS b_type
FROM a JOIN b ON a.id = b.ref_id LIMIT 1;

Pitfalls

The type is resolved at plan time. Wrapping the argument in a cast (for example, `PG_TYPEOF(CAST(x AS BIGINT))`) returns the cast target type regardless of x's actual type.
For NULL literals the returned type depends on the inferred context. `PG_TYPEOF(NULL)` returns `unknown` in an isolated query; assign a context to force a specific type.
PG-compat names differ from the dialect-neutral names reported by TYPEOF. Pick one consistently when writing schema-comparison tooling.
The function does not recurse into composite types. For an array or struct column, the output names the top-level type only.