Difficulty: Intermediate
Estimated Time: 20 minutes

In this tutorial you will connect to Postgres using the psql command line interface. You will write a WINDOW clause and use it with some window functions. You will also order, partition, and inline the window definition.

Commands to run are shown in grey boxes throughout the tutorial. Click on the boxes or copy and paste the commands into the terminal to execute each command.

The data we will be using for the examples comes from the pagila sample database available on GitHub. We will look at customer names and the stores they shop at.

Congratulations! Using Postgres you have now learned how to:

  • Specify a WINDOW clause
  • Order and partition it
  • Inline the definitions

You also learned these window functions:

  • row_number()
  • rank()
  • dense_rank()
  • lag(expression, offset, default)
  • lead(expression, offset, default)

There are more window functions that you can use. Please refer to the Postgres documentation.

In the next part of this series, you will learn about window frames.

Window Functions in Postgres, Part I

Step 1 of 5

Step 1 - Connect to Postgres

By default on a Linux system, Postgres' authentication is setup to allow trusted access from the postgres user account that the database server runs under. So, let's become the postgres user:

su - postgres

Now, run the psql command line interface for Postgres. The default values for user, host and port will work in this case so we don't need to provide any command line options. For more information on the psql command line tool, please see the psql documentation:

psql pagila

You should now see the psql command prompt and be able to move to the next step.