Return to Answer

added 496 characters in body

edited Feb 5, 2015 at 12:23

1.2k
9
6

\d+ to list all tables in current search_path andschema in current database (usually it's postgres schema).

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)

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

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

Source Link

answered Sep 15, 2014 at 19:42

Soni Harriz

1.2k
9
6

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)