Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List all tables in postgresql information_schema

People also ask

How do I see all tables in PostgreSQL?

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.

Does Postgres have INFORMATION_SCHEMA?

PostgreSQL provides an information_schema schema that contains views that return information about Postgre objects. If the user has the appropriate access, the user can also query tables or views in the pg_catalog schema to get information about Postgres objects.


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


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!