PostgreSQL: Show tables in PostgreSQL

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.

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

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

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.

Running psql with the -E flag will echo the query used internally to implement \dt and similar:

sudo -u postgres psql -E

postgres=# \dt       
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name", 
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
    AND n.nspname <> 'pg_catalog'
    AND n.nspname <> 'information_schema'
    AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;        
**************************