List all tables in postgresql information_schema
SqlPostgresqlInformation SchemaSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
For listing your tables use:
SELECT table_name FROM information_schema.tables WHERE table_schema='public'
It will only list tables that you create.
Solution 3 - Sql
\dt information_schema.
from within psql, should be fine.
Solution 4 - Sql
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)
Solution 5 - Sql
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'
Solution 6 - Sql
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).
Solution 7 - Sql
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
Solution 8 - Sql
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)