CODEWITHSUNDEEP
sqlpostgresqlinformation-schema
littleK
littleK

Reputation: 20123

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.

Upvotes: 309

Views: 691245

Answers (9)

V.J.
V.J.

Reputation: 928

select * from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE'

to get your tables only

Upvotes: 1

hzh
hzh

Reputation: 351

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'

Upvotes: 19

Sally Levesque
Sally Levesque

Reputation: 121

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)

Upvotes: 3

germanlinux
germanlinux

Reputation: 2511

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

Upvotes: 11

Timofey
Timofey

Reputation: 2508

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

Upvotes: 11

Chris Shoesmith
Chris Shoesmith

Reputation: 229

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)

Upvotes: 22

phsaires
phsaires

Reputation: 2378

For listing your tables use:

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

It will only list tables that you create.

Upvotes: 177

user80168
user80168

Reputation: 

\dt information_schema.

from within psql, should be fine.

Upvotes: 51

RodeoClown
RodeoClown

Reputation: 13788

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.

Upvotes: 397

Related Questions