Friday, February 14, 2020

How to list schemas in a PostgreSQL database



Use the "\dn" or "\dn+" for more details. They will list the schemas in the database you are connected to.

When you simply type "psql" at your prompt, you will by default be connected to the "postgres" database.

Your output will be something like this:
[postgres@oric-pgdb01.oric.no ~]$ psql
psql (11.6)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \dn
       List of schemas
       Name        |  Owner
-------------------+----------
 postgres_exporter | postgres
 public            | postgres
(2 rows)

If you have multiple databases on your postgres server, you must connect to the proper one to see the schemas you're interested in.
For example:

[postgres@oric-pgdb01.oric.no ~]$ psql
psql (11.6)
Type "help" for help.

postgres=# \connect klmdb superuser 
or, suppling detailed information to psql directly on the command-line:

-h is the FQDN server
-U is the username
"klmdb" is the database name:

[postgres@oric-pgdb01.oric.no ~]$ psql -h oric-pgdb01.oric.no -U superuser klmdb
Then:
klmdb=# \dn
 klm         | klm_migrator
 public      | postgres
 klm_inv     | klm_inventory

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete