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.

score 363 · Accepted Answer · answered Feb 16 '10 at 22:08

363

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.

answered Feb 16 '10 at 22:08

RodeoClown

13,338
13
52
56

5

Thanks, I just tried: /dt (asterisk).(asterisk) is that any different? – littleK Feb 16 '10 at 22:10
1

I don't know anything about the /dt (asterisk).(asterisk) thing, sorry. I just ran the select query in postgres and it listed information about all the tables in it. Try running the select statement (on your blank db) and see what it returns. – RodeoClown Feb 16 '10 at 22:15
Trying the above command lists the following tables in information_schema: sql_features, sql_implementation_info, sql_languages, sql_packages, sql_parts, sql_sizing, sql_sizing_profiles.....So what's the difference between those tables and the ones in information_schema.tables? – littleK Feb 16 '10 at 22:16
Running the select statement returns a lot more tables, i just don't know what the difference is... – littleK Feb 16 '10 at 22:19
2

All of the tables you have listed (via the /dt command) provide meta-information about the database. Each of the tables listed shows different information. So, for instance, the information_schema.tables table lists all the tables in the database and their attributes (such as being able to see whether it is a table or a view, what the name is and other information like that). The information_schema.sql_features table will show what features are enabled on the database (so I can see that I have Embedded C supported on my database, as well as direct SQL). – RodeoClown Feb 16 '10 at 22:20
1

You can run a select * on each of the tables listed by the dt command - it has just shown you a list of tables containing meta-data on the database. – RodeoClown Feb 16 '10 at 22:21
How we can find the db of these tables or we can any way join below queries SELECT * FROM pg_catalog.pg_tables; SELECT * FROM pg_catalog.pg_database; Actually, I want to find tables in specific DB @https://stackoverflow.com/users/943/rodeoclown – Neeraj Sharma May 30 '19 at 08:16

score 159 · Answer 2 · edited Nov 25 '12 at 19:21

159

For listing your tables use:

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

It will only list tables that you create.

edited Nov 25 '12 at 19:21

alf

18,372
10
61
92

answered Aug 30 '12 at 13:52

phsaires

2,188
1
14
11

How about tables that you don't create but you have access permission to? – huy Mar 06 '13 at 09:07
5

This will only show tables in the public schema. You can create tables in other schemas. – Joe Van Dyk Jul 12 '13 at 20:43
Also this won't distinguish between tables and views. – jayarjo May 07 '20 at 11:43
@JoeVanDyk I think the fact the you have the where clause literally tells you that. This helps those who would want to list all tables in just one or more schema – Aakash Verma Jul 11 '22 at 14:52

score 50 · Answer 3 · answered Feb 16 '10 at 22:31

50

\dt information_schema.

from within psql, should be fine.

answered Feb 16 '10 at 22:31

score 21 · Answer 4 · answered Jul 07 '14 at 06:25

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)

This doesn't list tables in schemas other than *public*. – Kenny Evitt Aug 02 '18 at 00:19 — Kenny Evitt, Aug 02 '18 at 00:19

score 16 · Answer 5 · answered Jan 21 '19 at 01:34

16

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'

answered Jan 21 '19 at 01:34

hzh

301
2
8

If you don't filter by table_type, you'll get all sort of objects, such as tables and views mixed up. – joninx Jul 19 '19 at 08:08
What exactly `where table_schema not in ('information_schema', 'pg_catalog')` is for? – jayarjo May 07 '20 at 11:45
1

@russellhoff I Did say "get all tables and views" in step 2. – hzh Jul 18 '20 at 05:47
@jayarjo I used "not in ('information_schema', 'pg_catalog')" to get USER tables and views only. – hzh Jul 18 '20 at 05:49

Timofey · Answer 6 · 2015-07-28T16:36:40.123

11

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

edited Jul 28 '15 at 16:36

answered May 16 '13 at 07:09

Timofey

2,478
3
37
53

score 11 · Answer 7 · edited Apr 03 '17 at 20:20

11

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

edited Apr 03 '17 at 20:20

Chris

8,527
10
34
51

answered Mar 01 '14 at 17:43

germanlinux

2,501
1
20
8

score 2 · Answer 8 · answered Feb 07 '18 at 11:46

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)

score 0 · Answer 9 · answered Jan 03 '23 at 12:47

0

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

to get your tables only

answered Jan 03 '23 at 12:47

V.J.

918
3
18
37

List all tables in postgresql information_schema

9 Answers9

Linked