List all tables in postgresql information_schema

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.

8 Answers

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.

For listing your tables use:

SELECT table_name FROM information_schema.tables WHERE table_schema='public' 

It will only list tables that you create.

\dt information_schema. 

from within psql, should be fine.

The "\z" COMMAND is also a good way to list tables when inside the interactive psql session.

eg.

# psql -d mcdb -U admin -p 5555 mcdb=# /z Access privileges for database "mcdb" Schema | Name | Type | Access privileges --------+--------------------------------+----------+--------------------------------------- public | activities | table | public | activities_id_seq | sequence | public | activities_users_mapping | table | [..] public | v_schedules_2 | view | {admin=arwdxt/admin,viewuser=r/admin} public | v_systems | view | public | vapp_backups | table | public | vm_client | table | public | vm_datastore | table | public | vmentity_hle_map | table | (148 rows) 

1.get all tables and views from information_schema.tables, include those of information_schema and pg_catalog.

select * from information_schema.tables 

2.get tables and views belong certain schema

select * from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog') 

3.get tables only(almost \dt)

select * from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog') and table_type = 'BASE TABLE' 

You may use also

select * from pg_tables where schemaname = 'information_schema' 

In generall pg* tables allow you to see everything in the db, not constrained to your permissions (if you have access to the tables of course).

For private schema 'xxx' in postgresql :

SELECT table_name FROM information_schema.tables WHERE table_schema = 'xxx' AND table_type = 'BASE TABLE' 

Without table_type = 'BASE TABLE' , you will list tables and views

If you want a quick and dirty one-liner query:

select * from information_schema.tables

You can run it directly in the Query tool without having to open psql.

(Other posts suggest nice more specific information_schema queries but as a newby, I am finding this one-liner query helps me get to grips with the table)