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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

dt information schema    from within psql  should be fine

User · Answer

For listing your tables use   SELECT table name FROM information schema tables WHERE table schema  public    It will only list tables that you create

User · Answer

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

[sql] List all tables in postgresql information_schema

Examples related to sql

Examples related to postgresql

Examples related to information-schema