Does PostgreSQL have an equivalent function for determining column metadata?
Reference: SQL_VARIANT_PROPERTY
pg_typeof() appears to be close, but it does not show the precision and scale. Looking up the type in pg_type only shows the generic type info, not the column specific, e.g. SQL Fiddle.
Best Answer
The fact that
pg_typeof
doesn't show the typmod is frustrating.To get the fully qualified type you can query the system catalogs. Let's look at how
psql
does it usingpsql -E
:This query can clearly be trimmed if you're not interested in the field collation,
NOT NULL
status, etc. We can also change theattrelid
filter from an oid to a regclass filter:producing:
This will only work on relations. I'm not aware of a reliable, generic way to get the typmod-qualified type for any result set, and it's a serious irritation. Among other things it makes it hard for the PostgreSQL JDBC driver to be compliant.