query to identify all data types used in postgresql database tables

Question

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 )

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 ;

• francs: very good answer.
• geotheory: 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
• sufleR: @geotheory look on extended answer.
• geotheory: 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 :)
• Incerteza: what type does count(*) return: int or bigint?