PostgreSQL: Show tables in PostgreSQL

ID : 408

viewed : 152

Tags : postgresqlpostgresql

Top 5 Answer for PostgreSQL: Show tables in PostgreSQL

vote vote

97

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.

vote vote

85

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.

vote vote

70

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
vote vote

67

(For completeness)

You could also query the (SQL-standard) information schema:

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

55

  1. First login as postgres user:

    sudo su - postgres

  2. connect to the required db: psql -d databaseName

  3. \dt would return the list of all table in the database you're connected to.

Top 3 video Explaining PostgreSQL: Show tables in PostgreSQL

Related QUESTION?