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?

Terbecki

Answer 1

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';

Mastat

What is a table type? – Obtrusive

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 – Rooted

Answer 2

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;

Hiss

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. – Pharr

Documentation says only the current one: 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)" – Pharr

Answer 3

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.

Island

@wingedpanther how to? there are a \d option to list only all tables, with no index, no seq, ...? – Impedance

Is \dt not there for this? – Patchouli

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. – Justinjustina

Answer 4

Try this:

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

this one works!

Shoshone

I don't think we need table_type='BASE TABLE'. Perhaps I am wrong, could you please elaborate? – Secretive

sometimes is useful filtering current connected DB adding: and table_catalog = current_database() – Oribella

Answer 5

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

Georgetown

Answer 6

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

Footlight

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. – Lucite

Answer 7

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

Mazuma

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. – Suint

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) – Procrastinate

@Procrastinate Good one! – Mazuma

Answer 8

Yes one may obtain a list of user defined tables present in a database using a query like this:

SELECT
  table_name
FROM
  information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_type='BASE TABLE'

Or

SELECT
  tablename
FROM
  pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')

As reads in the official docs:

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types...

So it's a good target for filtering out the user-defined stuff from the builtin ones. Stay aware that since its possible to define tables in schemas other than the public schema, relying on filtering of the results on table_schema as suggested by the other people and also in other places like the google cloud's docs, is not a good option, because it may neglect the user-defined tables on the other builtin or user-defined schema.

Serena

This should be SELECT tablename, not table_name. Documentation: postgresql.org/docs/current/view-pg-tables.html. Won't let me edit, says too many edits pending. – Poultice