Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

6 Answers 6

up vote 222 down vote accepted

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.

share|improve this answer
You can use \c db_name to connect to a certain database. –  eikes Feb 17 '13 at 8:57
\dt doesn't appear to list all tables in the current database (it seems to exclude those that aren't found in the search_path at least on 9.2) –  Jack Douglas Aug 6 '13 at 19:42

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;
share|improve this answer
You're right, but the question was about the meta-commands of the psql-tool. \dt is much easier than typing any query. –  Frank Heikens Feb 18 '11 at 7:50
+1 because that's actually the answer I was looking for –  Will Sheppard Jun 4 '13 at 14:39
thanks a million.you saved a day for me. –  lakshman May 7 at 17:31

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

share|improve this answer
+1 for mentioning \? it might save SE from new questions about the meta-commands. –  Radu Maris Mar 8 '13 at 13:31
@RaduMaris Although I agree that this is a good suggestion, I doubt that SE benefits from people not posting new questions, would defeat the purpose IMO. –  Morgan Wilde Jun 5 '13 at 17:33

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 PSQL 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:


Use the command \d meaning show all tables, views, and sequences

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.

share|improve this answer
\d does not just list tables: \d[S+] list tables, views, and sequences –  Jack Douglas Aug 6 '13 at 19:43
to show only tables you can do \dt –  Roberto Arosemena Jul 4 at 20:04

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

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.

share|improve this answer
While this is true, this addresses a different client than the OP asked about. –  dezso Nov 29 '12 at 17:56

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 \dt. 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 \dt will show you the tables in schema data too.

share|improve this answer
Well, a simple set search_path=data, public; would do the trick, too :) –  dezso Apr 3 at 14:57
@dezso, does that make the change permanently, or just in that psql session? –  John Barça Apr 3 at 17:40
Err, I was not very clear. It was intended instead of the logout-login cycle. –  dezso Apr 3 at 18:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.