What is the best way to list all of the tables within PostgreSQL's information_schema?
To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table in the information_schema structure.
+1
A:
You should be able to just run select * from information_schema.tables to get a listing of every table being managed by Postgres for a particular database.
You can also add a where table_schema = 'information_schema' to see just the tables in the information schema.
RodeoClown
2010-02-16 22:08:06
Thanks, I just tried: /dt (asterisk).(asterisk) is that any different?
behrk2
2010-02-16 22:10:20
I don't know anything about the /dt (asterisk).(asterisk) thing, sorry. I just ran the select query in postgres and it listed information about all the tables in it.Try running the select statement (on your blank db) and see what it returns.
RodeoClown
2010-02-16 22:15:18
Trying the above command lists the following tables in information_schema: sql_features, sql_implementation_info, sql_languages, sql_packages, sql_parts, sql_sizing, sql_sizing_profiles.....So what's the difference between those tables and the ones in information_schema.tables?
behrk2
2010-02-16 22:16:50
Running the select statement returns a lot more tables, i just don't know what the difference is...
behrk2
2010-02-16 22:19:10
All of the tables you have listed (via the /dt command) provide meta-information about the database. Each of the tables listed shows different information.So, for instance, the information_schema.tables table lists all the tables in the database and their attributes (such as being able to see whether it is a table or a view, what the name is and other information like that). The information_schema.sql_features table will show what features are enabled on the database (so I can see that I have Embedded C supported on my database, as well as direct SQL).
RodeoClown
2010-02-16 22:20:56
You can run a select * on each of the tables listed by the dt command - it has just shown you a list of tables containing meta-data on the database.
RodeoClown
2010-02-16 22:21:58
Thanks for the explanation! That helps me out.
behrk2
2010-02-16 22:30:01
No worries, glad to help :)
RodeoClown
2010-02-16 22:32:11