List all tables in postgresql information_schema

user_123891 0
Viewed 484678

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.

information-schema postgresql sql
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.

RodeoClown_9430 
\dt information_schema.

from within psql, should be fine.

Admin1

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_88039730

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)

user_85675550
Related