Categories
postgresql

PostgreSQL: Show tables in PostgreSQL

2228

What’s the equivalent to show tables (from MySQL) in PostgreSQL?

1

3189

From the psql command line interface,

First, choose your database

\c database_name

Then, this shows all tables in the current schema:

\dt

Programmatically (or from the psql interface too, of course):

SELECT * FROM pg_catalog.pg_tables;

The system tables live in the pg_catalog database.

13

  • 117

    @StephenCorwin No, \l is the equivalent of show databases in MySQL. dtshow tables and lshow databases

    Aug 24, 2012 at 4:01


  • 17

    \dt is very useful. That pg_catalog.pg_tables one is much less so, as it appears to lump internal tables together with the user-created ones for whatever database you happen to be connected to.

    – aroth

    Jul 29, 2013 at 6:25

  • 35

    psql my_db_name should be run in order \dt to work. When I ran psql without a database name, I got a “No relations found” message

    Nov 19, 2013 at 15:01

  • 43

    Without system tables: SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'

    Jul 6, 2014 at 0:36


  • 45

    You first need \c <DATABASE_NAME> to choose your database.

    – danio

    Sep 26, 2016 at 15:40

223

Login as superuser:

sudo -u postgres psql

You can list all databases and users by \l command, (list other commands by \?).

Now if you want to see other databases you can change user/database by \c command like \c template1, \c postgres postgres and use \d, \dt or \dS to see tables/views/etc.

0

    191

    You can use PostgreSQL’s interactive terminal Psql to show tables in PostgreSQL.

    1. Start Psql

    Usually you can run the following command to enter into psql:

    psql DBNAME USERNAME
    

    For example, psql template1 postgres

    One situation you might have is: suppose you login as root, and you don’t remember the database name. You can just enter first into Psql by running:

    sudo -u postgres psql
    

    In some systems, sudo command is not available, you can instead run either command below:

    psql -U postgres
    psql --username=postgres
    

    2. Show tables

    Now in Psql you could run commands such as:

    1. \? list all the commands
    2. \l list databases
    3. \conninfo display information about current connection
    4. \c [DBNAME] connect to new database, e.g., \c template1
    5. \dt list tables of the public schema
    6. \dt <schema-name>.* list tables of certain schema, e.g., \dt public.*
    7. \dt *.* list tables of all schemas
    8. Then you can run SQL statements, e.g., SELECT * FROM my_table;(Note: a statement must be terminated with semicolon ;)
    9. \q quit psql

    1

    • “psql -U postgres” will log and connect to “postgres” database

      – Bhanu Tez

      Feb 20, 2021 at 0:13