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.
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_pathYou 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.
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;
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.
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.
\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.
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.
