Goals and Audience

This post expands on the introduction to PostgreSQL and introduces some helpful commands for exploring a PostgreSQL database.

In the introduction, you created a database called business and accessed the PSQL command prompt by running psql business. This post will reference the same database. If you did not create it already, you can either:

  • Run createdb business from your command line / terminal.
  • Run psql to enter the PSQL command prompt, then enter the command create database business;.

Helpful Commands

Commands in PostgreSQL begin with the backslash character \. While there are many commands available, the following will help you explore your database.

General Commands

  • \? shows all possible commands.
  • \q quits the PSQL command prompt.

Informational Commands

  • \dn shows you a list of schemas, which are like groups or namespaces for tables. By default, everything goes in the public schema. Some use cases require multiple schemas, and for now it’s okay to keep everything in public.

    • business=# \dn
List of schemas
Name  | Owner 
--------+-------
 public | eric
(1 row)

  • \dt shows you a list of tables. You can also try \dt+ if you want to see more detailed information. The output says List of relations, and in general a relation is another name for a table. There are other types of relations, but most of the time they are tables.

    • business=# \dt
  List of relations
Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | customers | table | eric
 public | purchases | table | eric
(2 rows)
    • The schema is the schema in which the table was created. For now, it will always be public.
    • The name is the name you provide when creating a table.
    • The type is the type of relation. In general this will be a table.
    • The owner is the user that created the table.

  • \l shows available databases. I refer to PostgreSQL as a database, but technically it is a relational database management system. The system can contain multiple databases, but for now you will use the business database.

    •   business=# \l
                           List of databases
     Name    | Owner | Encoding | Collate | Ctype | Access privileges 
  -----------+-------+----------+---------+-------+-------------------
   business  | eric  | UTF8     | C       | C     | 
   postgres  | eric  | UTF8     | C       | C     | 
   template0 | eric  | UTF8     | C       | C     | =c/eric          +
             |       |          |         |       | eric=CTc/eric
   template1 | eric  | UTF8     | C       | C     | =c/eric          +
             |       |          |         |       | eric=CTc/eric
  (4 rows)

    • You created the business database, and the other three are defaults that are created when PostgreSQL is installed.

    • Use \c to connect to a different database. For example, run \c postgres and then run \c business to switch back.

    • Use \conninfo to see which database you’re currently using:

      •   business=# \conninfo
  You are connected to database "business" as user "eric" via socket in "/tmp" at port "5432".

Formatting Commands

The following commands can help make PostgreSQL output easier to read.

  • \x formats row output vertically (called expanded output). It can be toggled on and off by entering the command repeatedly, or explicitly by entering \x on or \x off. You can also use \x auto if you want the database to decide. This setting is off by default. The following example shows the difference between when expanded output is off and when it is on.

    • business=# select * from purchases;
 customer_name | amount | purchase_date 
---------------+--------+---------------
 Eric          |  55.01 | 2020-04-20
 Eric          |  86.57 | 2020-04-21
 Melanie       |  57.50 | 2020-04-23
(3 rows)
    
business=# \x
Expanded display is on.
business=# select * from purchases;
-[ RECORD 1 ]-+-----------
customer_name | Eric
amount        | 55.01
purchase_date | 2020-04-20
-[ RECORD 2 ]-+-----------
customer_name | Eric
amount        | 86.57
purchase_date | 2020-04-21
-[ RECORD 3 ]-+-----------
customer_name | Melanie
amount        | 57.50
purchase_date | 2020-04-23
    
business=# \x
Expanded display is off.

  • \timing shows how long it takes a query to run, which can help you debug performance issues. Like \x, you can toggle it on and off or set it explicitly using \timing on or \timing off. This example shows a query that takes 0.386 milliseconds to execute.

    • business=# \timing
Timing is on.
business=# select * from purchases;
 customer_name | amount | purchase_date 
---------------+--------+---------------
 Eric          |  55.01 | 2020-04-20
 Eric          |  86.57 | 2020-04-21
 Melanie       |  57.50 | 2020-04-23
(3 rows)
    
Time: 0.386 ms
business=# \timing
Timing is off.
business=#

Summary

In this guide, you learned:

  • \? shows all possible commands.
  • \q quits the PSQL command prompt.
  • \dn shows a list of schemas, which are groupings of tables.
  • \dt shows a list of tables, which are a type of relation. In general, you can think of a relation and table as being the same.
  • \l shows a list of available databases. For now, you just have the business database we created. You also learned that PostgreSQL is a relational database management system.
    • \c lets you connect to a different database, such as \c postgres (to use the default postgres database), or \c business to switch back to your business database.
    • \conninfo shows the database you are currently using.
  • \x switches to expanded output mode, which formats rows vertically. This command is helpful when viewing tables with many columns.
  • \timing shows you how long a query takes to run, which is helpful for debugging performance issues. Although you won’t see performance issues yet, future posts will cover possible issues and how to debug them.