1563

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.

0

9 Answers 9

2026

Please note the following commands:

  • \list or \l: list all databases
  • \c <db name>: connect to a certain database
  • \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

See the manual about psql.

1
  • Note you can extend on the "Informational" commands to use wildcard searches. Full list of other options can be shown with \? \l *somestring* \dn *somestring* \dt *somestring*
    – Jay
    Commented Nov 9, 2023 at 5:01
515

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;
1
  • 59
    If you start psql with the -E flag, it will display the real query when you use a meta-command.
    – Deebster
    Commented Jan 13, 2016 at 9:03
149

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:

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.

0
85

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

0
46

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

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.

0
23

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.

0
8

I'm surprised nobody's mentioned this before, but these commands (from the manual here):

-E
--echo-hidden
    
    Echo the actual queries generated by \d and other backslash commands. You can 
use this to study psql's internal operations. This is equivalent to setting the 
variable ECHO_HIDDEN to on.

command(s) are a great way of learning the internals of PostgreSQL commands!

run psql -E <db_name> and then run (for example):

\dt

and the output is then:

********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' 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
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | int1test_1 | table | pol
 public | int1test_2 | table | pol
 public | x          | table | pol
(3 rows)

So, the -E flag can really give you insight into the internals of PostgreSQL!

Finally, Also, if you already started the psql client (and you don't want to exit and restart it with -E), you can use \set ECHO_HIDDEN to set the flag on.

Also, and again from the manual cited above, you can do a kind of dry run by setting the value to noexec:

If you set this variable to the value noexec, the queries are just shown but are not actually sent to the server and executed. The default value is off.

1

<Database>

This lists all databases:

SELECT datname FROM pg_database;

These list all databases in detail:

\list
\l

These list all databases in more detail:

\list+
\l+

<Table>

These list all tables of all schemas of the current database:

\dt *.*
\dtS *.*

These list all tables of all schemas of the current database in detail:

\dt+ *.*
\dtS+ *.*

These list all tables of pg_catalog and public schemas of the current database:

\dtS
\dtS *
\dt *

These list all tables of pg_catalog and public schemas of the current database in detail:

\dtS+
\dtS+ *
\dt+ *

This lists all tables of public schema of the current database:

\dt

This lists all tables of public schema of the current database in detail:

\dt+

These list all tables of my_schema schema of the current database:

\dtS my_schema.*
\dt my_schema.*

These list all tables of my_schema schema of the current database in detail:

\dtS+ my_schema.*
\dt+ my_schema.*

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