I don't want to know all data types, just all data types used in my database. Can this information be queried?

PostgreSQL 8.4 and 9.x versions

I currently need to know all data types for over 200 tables in public ( and other schemas )

1 Answer 1

select data_type, count(*) 
from information_schema.columns 
where table_schema = 'public' 
group by data_type ;

For other schemas just add: or table_schema = 'your_schema_name'.
If this will not satisfy you, you should look in pg_... or information_schema tables.

If you are looking for table information:

select data_type, count(*)
from information_schema.columns
where table_schema = 'public' and table_name = 'your_table_name'
group by data_type ;
  • 1
    very good answer.
    – francs
    Dec 6, 2012 at 2:55
  • What would be the equivalent for a specific table? Have tried replacing lines2/3 but I'm getting errors e.g.: could not Retrieve the result : ERROR: column "data_type" does not exist
    – geotheory
    Apr 2, 2014 at 10:05
  • @geotheory look on extended answer.
    – sufleR
    Apr 3, 2014 at 12:36
  • This is handy thanks. Ultimately I wonder about a query equivalent to the psql prompt \d table_name \g method, which lists fields and their datatypes, but since this method exists there's no urgency :)
    – geotheory
    Apr 3, 2014 at 22:10
  • what type does count(*) return: int or bigint?
    – Incerteza
    Apr 10, 2014 at 14:55

Your Answer

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

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