# How do I list all databases and tables using psql?

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.

• If you want to access it via the command line, run psql -l – adriaan Apr 22 '18 at 14:48
• This comment should definitely be one of the top answers! If you need auth you can also psql --username=postgres -l. – Ulysse BN Feb 22 '19 at 9:45

• \list or \l: list all databases
• \dt: list all tables in the current database using your search_path
• \dt *.: list all tables in the current database regardless your search_path

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).

To switch databases:

\connect database_name or \c database_name

• 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
• \dt *. will list all tables in all schemas, without having to modify your search path. – danpelota Mar 24 '15 at 16:10
• \l+ is my favorite - it shows the disk usage as well. – Lester Cheung May 21 '15 at 5:57
• On Windows I can list the databases with this command psql -U username -l but it doesnt work with the slash version. – NoNameProvided Oct 6 '15 at 15:37

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;

• 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
• I think this is a GREAT answer because it can be executed from a Linux command line as opposed to needing to be in the psql interpreter which sometimes hangs for me using ExtraPutty. – Love and peace - Joe Codeswell May 16 '15 at 17:51
• Also saved my day. For my particular case I'm adding WHERE table_schema = 'public' because I want to drop just custom tables. – Renra Sep 28 '15 at 15:31
• If you start psql with the -E flag, it will display the real query when you use a meta-command. – Deebster Jan 13 '16 at 9:03
• Yes, I think it's a good answer as it lets you join and query the databases in a more controlled way especially when you want to generate some script out of a big database list. Thanks. – Saim May 28 '19 at 20:51

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:

kurz_prod=#


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.

• \d does not just list tables: \d[S+] list tables, views, and sequences – Jack Douglas Aug 6 '13 at 19:43
• To me, this is the "correct" answer because it doesn't require you to be already connected to an existing database. – aardvarkk Mar 14 '17 at 15:11
• One liner for use in scripts (as root): su - -c 'psql -U postgres postgres -P pager=off -P tuples_only=on -l' postgres | cut -d'|' -f1 | tr -d '[:blank:]' | grep -vE '\$^' – user3132194 Sep 21 '20 at 7:38

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

To gain more info on database and table list, You can do :

\l+ to list databases

                                                                    List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
pgbench    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 29 MB   | pg_default |
postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6073 kB | pg_default | default administrative connection database
slonmaster | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1401 MB | movespace  |
slonslave  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 32 MB   | pg_default |
template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 5785 kB | pg_default | unmodifiable empty database
|          |          |             |             | postgres=CTc/postgres |         |            |
template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 5985 kB | pg_default | default template for new databases
|          |          |             |             | postgres=CTc/postgres |         |            |
test       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 13 MB   | pg_default |
(7 rows)


and

\d+ to list all tables in current search_path schema in current database.

test=# \dn+ --list schemas
List of schemas
Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
|          | =UC/postgres         |
schema1 | postgres | postgres=UC/postgres+|
|          | =UC/postgres         |
(2 row)

test=# set search_path to schema1, public;
SET
test=# \d+
List of relations
Schema  |      Name       | Type  |    Owner     |    Size    | Description
---------+-----------------+-------+--------------+------------+-------------
public  | all_units       | table | postgres     | 0 bytes    |
public  | asset           | table | postgres     | 16 kB      |
public  | asset_attribute | table | postgres     | 8192 bytes |
public  | food            | table | postgres     | 48 kB      |
public  | name_log        | table | postgres     | 8192 bytes |
public  | outable         | table | ordinaryuser | 0 bytes    |
public  | outable2        | table | ordinaryuser | 0 bytes    |
public  | test            | table | postgres     | 16 kB      |
public  | usr             | table | postgres     | 5008 kB    |
schema1 | t1              | table | postgres     | 0 bytes    |
(10 rows)


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.

• While this is true, this addresses a different client than the OP asked about. – dezso Nov 29 '12 at 17:56
• This worked great for me, and although my use case was not exactly what the OP asked for, it helped me get the table list while connected via wrapper (in Julialang LibPQ.jl) – Vass Aug 10 '19 at 15:52

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.

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