List all tables in postgresql information_schema

Question

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.

Answer 1

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.

Answer 2

For listing your tables use:

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

It will only list tables that you create.

Answer 3

\dt information_schema.

from within psql, should be fine.

Answer 4

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)

Answer 5

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).

Answer 6

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'

Answer 7

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

Answer 8

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)