PostgreSQL query to list all table names?

Question

Is there any query available to list all tables in my Postgres DB.

I tried out one query like:

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

But this query returns views also.

How can i get only table names only, not views?

score 448 · Answer 1 · answered Feb 06 '13 at 13:47

448

What bout this query (based on the description from manual)?

SELECT table_name
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE';

answered Feb 06 '13 at 13:47

vyegorov

21,787
7
59
73

3

What is a table type? – Bryan Bryce Sep 08 '18 at 23:28
2

table_type from the manual: Type of the table: BASE TABLE for a persistent base table (the normal table type), VIEW for a view, FOREIGN for a foreign table, or LOCAL TEMPORARY for a temporary table – tzachs Mar 11 '20 at 18:58

Harsh · Answer 2 · 2016-07-22T06:12:58.173

51

If you want list of database

SELECT datname FROM pg_database WHERE datistemplate = false;

If you want list of tables from current pg installation of all databases

SELECT table_schema,table_name FROM information_schema.tables
ORDER BY table_schema,table_name;

edited Jul 22 '16 at 06:12

answered Feb 27 '15 at 09:10

Harsh

2,893
29
16

At least in Postgres 9.5, that's not true. I've got 3 databases in one cluster, and this is only returning tables from the current database. – sudo Sep 10 '16 at 05:52
Documentation says only the current one: https://www.postgresql.org/docs/9.5/static/infoschema-tables.html "table_catalog sql_identifier Name of the database that contains the table (always the current database)" – sudo Sep 10 '16 at 05:53

score 33 · Answer 3 · edited Oct 13 '14 at 11:37

33

Open up the postgres terminal with the databse you would like:

psql dbname (run this line in a terminal)

then, run this command in the postgres environment

\d

This will describe all tables by name. Basically a list of tables by name ascending.

Then you can try this to describe a table by fields:

\d tablename.

Hope this helps.

edited Oct 13 '14 at 11:37

Vivek S.

19,945
7
68
85

answered Mar 26 '14 at 12:40

Ty_

828
1
11
21

@wingedpanther how to? there are a `\d` option to list *only* all tables, with no index, no seq, ...? – Peter Krauss Dec 03 '15 at 20:35
6

Is ```\dt``` not there for this? – thoroc Jan 14 '16 at 09:15
In my experience, wherever I needed to look for table list it was always because I wanted to see if a table exists. Or if do I have a table say for which "contact information" so may I want see all table with "contact" in the name which might come at the beginning, middle, end , anywhere or be just "contact" so I always found schema-vele query to be helpful in that case i.e. SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' and table_name like '%contact%; Rather than going through the list of tables in ascending order. – nanosoft Mar 24 '21 at 06:02

score 17 · Answer 4 · edited Aug 21 '18 at 19:17

17

Try this:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema='public' AND table_type='BASE TABLE'

this one works!

edited Aug 21 '18 at 19:17

C B

1,677
6
18
20

answered Aug 21 '18 at 17:54

T.S.

171
1
3

I don't think we need `table_type='BASE TABLE'`. Perhaps I am wrong, could you please elaborate? – abriggs May 31 '19 at 15:00
sometimes is useful filtering current connected DB adding: *and table_catalog = current_database()* – Diego Scaravaggi Nov 28 '19 at 13:01

score 14 · Answer 5 · answered Dec 27 '19 at 04:06

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.

Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).

If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].

score 10 · Answer 6 · answered Aug 04 '16 at 13:32

10

How about giving just \dt in psql? See https://www.postgresql.org/docs/current/static/app-psql.html.

answered Aug 04 '16 at 13:32

Baris Demiray

1,539
24
35

Yes, this gives the list of `tables` within the connected `database`, not the content of a particular `table` within the database. This is the answer to the question asked here. – Gathide Jul 13 '22 at 07:19

Vivek S. · Answer 7 · 2014-10-13T11:57:01.190

9

select 
 relname as table 
from 
 pg_stat_user_tables 
where schemaname = 'public'

This will not work if track_activities is disabled

select 
  tablename as table 
from 
  pg_tables  
where schemaname = 'public'

Read more about pg_tables

edited Oct 13 '14 at 11:57

answered Oct 13 '14 at 11:36

Vivek S.

19,945
7
68
85

1

`pg_stat_user_tables` might not be populated if `track_activities` is disabled. Using the "official" API such as `pg_tables` or `information_schema.table` is a **much** better choice. – Oct 13 '14 at 11:54
1

side note to this old answer. It looks like information_schema.table only gives table names where you have some access permission. pg_tables allowed me to see names of all tables, even ones I did not have access to. (was useful in an interaction with an administrator) – JerryD Sep 15 '21 at 17:01
@JerryD Good one! – Vivek S. Sep 16 '21 at 10:08

PostgreSQL query to list all table names?

7 Answers7

Linked