When working with cloud managed db servers, we might have to query db through the server terminal because in higher environments like PROD the server and its ports are not exposed outside the network. In other cases, we can establish connection to db server through any DB UI clients like PgAdmin or IDE plugins or vendor specific db command line clients. Here is the list of basic psql CLI commands that I often use.

  1. psql -h <dbhost> -p <port> -U <dbuser> -d <db>
    • To connect to database with psql CLI client
    • Note: There is no password option, it will prompt. For more info refer psql --help
  2. \conninfo
    • Displays connection information
  3. \list
    • Lists all the databases in this connection
  4. \connect <db> or \c <db>
    • Switch to other database from the current psql CLI client prompt
  5. \dt
    • List all the tables in the database
  6. \dv
    • List all the views in the database
  7. create database <db> or createdb <db>
    • To create new db
  8. \q
    • Helps to disconnect from the connection and quit from psql prompt
  9. \! cls
    • To clear the screen in Windows
    • Note: In Linux based OS use \! clear

Note:

  • Always add ; at the end of each command to let psql CLI client understand that it’s the end of the command.
  • Additionally, in psql if your table name contains uppercase letters, special characters, or spaces, you might need to use double quotes around the table name in SQL query. Otherwise, it complains ERROR: relation "<your table/view>" does not exist