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

7 Answers 7

up vote 250 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
35  
You can use \c db_name to connect to a certain database. –  eikes Feb 17 '13 at 8:57
4  
\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

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 and current database (usually it's postgres schema)

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)
share|improve this answer

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

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.

share|improve this answer
2  
\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:

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.

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

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
3  
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
3  
+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
3  
+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

Your Answer

 
discard

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.