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?

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

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;

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.

Answer 4

Try this:

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

this one works!

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

Answer 6

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

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

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.

Answer 9

Since each connection is specific to a database, you can try running a shell script.

This script will select all the database names and then loop over them, listing all their tables.

#!/bin/bash

USERNAME=postgres
DBNAME=postgres  # Initially connect to and get the list of databases

# Get a list of all databases except system databases, remove leading/trailing whitespace
databases=$(psql -U $USERNAME -d $DBNAME -t -A -c "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname ASC;")

# Loop over each database and run the query
for db in $databases; do
    echo "Database: $db"
    echo "--------------------------------"

    # Disable pager, remove headers, and run the query to fetch tables
    tables=$(psql -U $USERNAME -d "$db" -P pager=off -t -A -c "SELECT tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY tablename ASC;")

    if [ -z "$tables" ]; then
        echo "  (No tables found)"
    else
        # Loop through the tables and format output
        for table in $tables; do
            echo "  - $table"
        done
    fi

    echo
done

I created this script via:

touch /bin/showtables
chmod +x /bin/showtables
vi /bin/showtables # paste script (above) and modify if needed

I ran the command above inside a postgres pod.