Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

What about

select column_name, DATA_TYPE,NUMERIC_PRECISION, NUMERIC_SCALE 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='YourTable' 

?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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