Sunday, 3 December 2017

Basic Postgres Command to list databases and list Schema

How to List Databases and Tables in PostgreSQL Using psql


First login to PostgreSQL using psql command and run following command from PostgreSQL command prompt.

 psql -U postgres

PostgreSQL command line cheatsheet:
Some interesting flags (to see all, use -h):
  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)
Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*
  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition including triggers
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \l: List databases
  • \dn: List schemas
  • \df: List functions
  • \dv: List views
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables

Listing Databases

A single Postgres server process can manage multiple databases at the same time. Each database is stored as a separate set of files in its own directory within the server’s data directory. To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l.

Listing Tables

Once you’ve connected to a database, you will want to inspect which tables have been created there. This can be done with the \dt meta-command. However, if there are no tables you will get no output.
postgres=# connect sampledb
sampledb=# dt


More Example Coming Soon..

17 comments:

  1. Thank you for your guide to with upgrade information about Hadoop
    Hadoop administration Online Course

    ReplyDelete
  2. Cool and I have a neat proposal: How Much House Renovation Cost Philippines house renovation

    ReplyDelete