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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 )

share|improve this question
up vote 9 down vote accepted
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 ;
share|improve this answer
Thanks, will check this out in the morning – Phill Pafford Dec 6 '12 at 2:00
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

Your Answer


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.