PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY

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.

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 using psql -E:

$ psql -E regress
psql (9.2.1)
Type "help" for help.

regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) );
CREATE TABLE
regress=> \d typmodtest

... tons of information printed, including this query which produces the information we want:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '24641' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

This query can clearly be trimmed if you're not interested in the field collation, NOT NULL status, etc. We can also change the attrelid filter from an oid to a regclass filter:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'typmodtest'::regclass 
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

producing:

attname |      format_type      
---------+-----------------------
 a       | numeric(16,2)
 b       | character varying(32)

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.

StackExchange Database Administrators Q#28158, answer score: 3