How do I list all databases and tables using psql?

Question

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.

When I log in with psql --username=postgres, how do I list all databases and tables?

I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.

Answer 1

Please note the following commands:

• \list lists all databases
• \dt lists all tables in the current database

You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).

See the manual about psql.

Answer 2

\l is also shorthand for \list. There are quite a few slash commands, which you can list in psql by using \?.

Answer 3

This lists databases:

SELECT datname FROM pg_database
WHERE datistemplate = false;

This lists tables in the current database

SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

Answer 4

In Postgresql these terminal commands list the databases available

el@defiant$ /bin/psql -h localhost --username=pgadmin --list

Or the command stated more simply:

psql -U pgadmin -l

Those commands print this on the terminal:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 kurz_prod | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 pgadmin   | pgadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

These are the available databases.

In PLSQL these commands list the tables available

You have to specify a database before you can list the tables in that database.

el@defiant$ psql -U pgadmin -d kurz_prod

This brings you to a psql terminal:

kurz_prod=#

Use the command \d meaning show all tables:

kurz_prod=# \d

This prints:

           List of relations
Schema |  Name   |   Type   |  Owner
--------+---------+----------+---------
public | mytable | table    | pgadmin
public | testing | sequence | pgadmin
(2 rows)

Then, to exit the psql terminal, type \q and press enter. Or Ctrl-D does the same thing. These are the tables in that database.

Answer 5

From pg_Admin you can simply run the following on your current database and it will get all the tables for the specified schema:

SELECT * 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
    AND table_schema = 'public' 
ORDER BY table_type, table_name

This will get you a list of all the permanent tables (generally the tables you're looking for). You can get just the table names if you change the * wildcard to just the table_name. The public table_schema is the default schema for most databases unless your admin has set up a new schema.

Answer 6

Ok, here's what I used on my command line (Ubuntu):

psql --list

Hope this works for you.

Answer 7

It is possible that you have inserted the tables into a schema that is not in your search path, or the default, ie, public and so the tables will not show up using \d. If you use a schema called, say, data, you can fix this by running,

alter database <databasename> set search_path=data, public;

Exit and reenter psql and now \d will show you the tables in schema data too.