How do I list all databases and tables using psql?

Question

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.

DimiDak · Accepted Answer · 2021-06-04 09:33:48Z

2041

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.

edited Jun 4, 2021 at 9:33

DimiDak

1033 bronze badges

answered Feb 17, 2011 at 9:36

Frank Heikens

23.8k1 gold badge28 silver badges20 bronze badges

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
Can I combine these options, like \dt and \df at the same time? Currently I can redirect to a file using \o then list all I want via several commands, wondering if it can be done in one command.
– Rafs
Commented Aug 1 at 13:40

Add a comment |

RolandoMySQLDBA · Accepted Answer · 2012-07-21 18:44:34Z

518

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;

edited Jul 21, 2012 at 18:44

answered Feb 17, 2011 at 18:30

RolandoMySQLDBA

184k33 gold badges320 silver badges525 bronze badges

61

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

Add a comment |

Eric Leschinski · Accepted Answer · 2013-11-08 15:34:58Z

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.

Derek Arnold · Accepted Answer · 2011-02-17 15:00:52Z

86

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

answered Feb 17, 2011 at 15:00

Derek Arnold

1,1666 silver badges4 bronze badges

Add a comment |

Soni Harriz · Accepted Answer · 2015-02-05 12:23:01Z

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)

András Váczi · Accepted Answer · 2012-11-29 17:55:31Z

39

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.

edited Nov 29, 2012 at 17:55

András Váczi

31.5k13 gold badges101 silver badges148 bronze badges

answered Nov 29, 2012 at 17:44

Larry W

3993 silver badges2 bronze badges

Add a comment |

John Powell · Accepted Answer · 2014-04-03 14:48:34Z

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.

edited Apr 3, 2014 at 14:48

answered Feb 8, 2013 at 15:49

John Powell

1,62815 silver badges19 bronze badges

Add a comment |

Vérace · Accepted Answer · 2023-02-14 13:26:41Z

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.

Super Kai - Kazuya Ito · Accepted Answer · 2023-09-26 13:02:37Z

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

Stack Exchange Network

How do I list all databases and tables using psql?

9 Answers 9

In Postgresql these terminal commands list the databases available

In PSQL these commands list the tables available

<Database>

<Table>

Not the answer you're looking for? Browse other questions tagged
postgresql
tools
psql
command-line
or ask your own question.

Linked

Hot Network Questions

How do I list all databases and tables using psql?

9 Answers 9

In Postgresql these terminal commands list the databases available

In PSQL these commands list the tables available

<Database>

<Table>

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

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
tools
psql
command-line
or ask your own question.