Does PostgreSQL have an equivalent function for determining column metadata?


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.

4 Answers 4


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) );
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;


 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.


Since PostgreSQL 9.2, there is also collation for (any) to get the collation (if any) - which is one of the 6 properties you can retrieve with SQL_VARIANT_PROPERTY in SQL Server.
Works universally, not just for table columns.

You'll find more on format_type() at the same page of the manual.
@Craig demonstrated how to use it.


What about

where TABLE_NAME='YourTable' 



PostgreSQL 11 with psql

I believe now you can just use \gdesc

    NULL AS zero,
    1 AS one,
    2.0 AS two,
    'three' AS three,
    $1 AS four,
    sin($2) as five,
    'foo'::varchar(4) as six,
 Column |         Type         
 zero   | text
 one    | integer
 two    | numeric
 three  | text
 four   | text
 five   | double precision
 six    | character varying(4)  -- XXX: notice the (4)
 now    | date
(8 rows)

Patch: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=49ca462eb165dea297f1f110e8eac064308e9d51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.