pg_typeof Function
beginnerpostgresql
2 min read
What is pg_typeof?
pg_typeof(expression) returns the data type of any SQL expression as a regtype value. It is invaluable for debugging type mismatches and understanding how PostgreSQL interprets your expressions.
Syntax
SELECT pg_typeof(expression);When to Use
- Debugging unexpected type errors or implicit cast behavior
- Verifying the type of a column or computed expression
- Learning how PostgreSQL handles literal types
- Checking the return type of functions
Key Points
- Any Expression — Works on columns, literals, function results, and computed values.
- Returns regtype — The result is a
regtypevalue, which displays as a readable type name. - Literals — PostgreSQL assigns default types to literals: 42 is integer, '42' is unknown (text context), 3.14 is numeric.
- No Side Effects — pg_typeof only inspects the type; it does not modify anything.
- Debugging Tool — Essential when debugging CAST errors or unexpected function behavior.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
pg_typeof Function Challenge
Write a query that solve this task: determine the types of columns in the students table.
Expected result
Shows data types like integer, character varying, integer.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is pg_typeof?
Initializing database...Each run starts from fresh sample data.
More Examples
Check literal and expression types
See how PostgreSQL types various expressions.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Is pg_typeof a PostgreSQL-specific function?
Yes. pg_typeof is a PostgreSQL extension. Other databases have equivalents like typeof() or similar.
Can I use pg_typeof in a WHERE clause?
Technically yes, but it is primarily a debugging tool. Filtering by type is unusual in production queries.