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 )

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 '12 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 '14 at 10:05
  • @geotheory look on extended answer.
    – sufleR
    Apr 3 '14 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 '14 at 22:10
  • what type does count(*) return: int or bigint? Apr 10 '14 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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