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.
Best Answer
There was a proposal for an extension that would do part of this, but it never got finalized. Perhaps another push could get it done.