Postgres Up and Running

April 2021

What is PostgreSQL

PostgreSQL (or simply Postgres), is a free and open-source relational database management system (RDBMS) in development since 1982. The project is a continuation of Ingres , which in turn started to be developed in the early 1970s.

Source Materials for this blog post:

1. Practical SQL Github
2. PostgreSQL 13.2 Documentation
3. SQL Cheat Sheet

The Relational Model

• Relational means it is a system for managing data stored in relations. Relation is essentially a mathematical term for table.
• Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type.
• Whereas columns have a fixed order in each row, it is important to remember that SQL does not guarantee the order of the rows within the table in any way (although they can be explicitly sorted for display).
• Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster.

Architectural Fundamentals

• Client/Server model:
  • Server process, which manages the database files, accepts connections to the database from client applications, and performs database actions on behalf of the clients. The database server program is called postgres.
  • Client (frontend) application that wants to perform database operations. Client applications can be very diverse in nature: a client could be a text-oriented tool, a graphical application, a web server that accesses the database to display web pages, or a specialized database maintenance tool.
• As is typical of client/server applications, the client and the server can be on different hosts. In that case they communicate over a TCP/IP network connection.
• The PostgreSQL server can handle multiple concurrent connections from clients. To achieve this it starts (“forks”) a new process for each connection. From that point on, the client and the new server process communicate without intervention by the original postgres process. Thus, the master server process is always running, waiting for client connections, whereas client and associated server processes come and go.

Constraints

Check Constraints

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

-- column constraint
CREATE TABLE products (
    product_no integer,
    name text,
    -- price numeric CHECK (price > 0) -- alternative w/o separate name
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

-- column and table constraints
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),  -- column constraint
    discounted_price numeric CHECK (discounted_price > 0), -- column constraint
    CHECK (price > discounted_price)  -- table constraint
    -- CONSTRAINT valid_discount CHECK (price > discounted_price)  -- with name
);

It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.

Not-Null Constraints

A not-null constraint simply specifies that a column must not assume the null value. A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way. Of course, a column can have more than one constraint. Just write the constraints one after another:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

Unique Constraints

Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns (ie. the null value is in fact the differentiator).

CREATE TABLE products (
    product_no integer UNIQUE,  -- column constraint format
    -- product_no integer CONSTRAINT must_be_different UNIQUE,  -- with name
    name text,
    price numeric
    -- UNIQUE (product_no)  -- table constraint format
);

NOTE: A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

Primary Key

A primary key is a column or collection of columns whose values uniquely identify each row in a table. A valid primary key column enforces certain constraints:

1. The column or collection of columns must have a unique value for each row.
2. The column or collection of columns can’t have nulls (unlike a Unique Constraint).

NOTE: Primary key values only need to be unique within a table.

Natural keys

• The data already exists in the table, and you don’t need to add a column to create a key. Eg. license plates within a State; student IDs within a university, etc.
• Because the natural key data has meaning, it can reduce the need to join tables when searching.

Surrogate keys

• Because a surrogate key doesn’t have any meaning in itself and its values are independent of the data in the table, if your data changes later, you’re not limited by the key structure.
• Natural keys tend to consume more storage than the integers typically used for surrogate keys.
• Universally unique identifiers (UUID) are a great way to create surrogate ids.

Adding a Primary Key

-- column constraint format
CREATE TABLE natural_key_example (
    -- license_id varchar(10)  PRIMARY KEY, -- w/o separate name
    license_id varchar(10) CONSTRAINT license_key PRIMARY KEY,
    first_name varchar(50),
    last_name varchar(50)
);

-- table constraint format
CREATE TABLE natural_key_example (
    license_id varchar(10),
    first_name varchar(50),
    last_name varchar(50),
    CONSTRAINT license_key PRIMARY KEY (license_id)
);

Composite Primary Keys

If a single column is not enough to create a unique natural key, two or more columns could be utilized as a composite primary key.

-- Composite keys must be created using the table constraint format
CREATE TABLE natural_key_composite_example (
    student_id varchar(10),
    school_day date,
    present boolean,
    CONSTRAINT student_key PRIMARY KEY (student_id, school_day)
);

Creating an Auto-Incrementing Surrogate Key

-- creating the table
CREATE TABLE surrogate_key_example (
    order_number bigserial,
    product_name varchar(50),
    order_date date,
    CONSTRAINT order_key PRIMARY KEY (order_number)
);

-- inserting records
-- order_number column is omitted. Counts from 1 and auto-increments +1
INSERT INTO surrogate_key_example (product_name, order_date)
VALUES ('Beachball Polish', '2015-03-17'),
       ('Wrinkle De-Atomizer', '2017-05-22'),
       ('Flux Capacitor', '1985-10-26');

Index Numbering Watchout

Even though a column with a serial type auto-increments each time a row is added, some scenarios will create gaps in the sequence of numbers in the column. If a row is deleted, for example, the value in that row is never replaced. Or, if a row insert is aborted, the sequence for the column will still be incremented.

Foreign Key

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. A foreign key must reference columns that either are a primary key or form a unique constraint. Unlike a primary key, a foreign key column can be empty, and it can contain duplicate values.

Creating a Foreign Key

In the registrations table, we designate the column license_id as a foreign key by adding the REFERENCES keyword, followed by the table name and column for it to reference.

CREATE TABLE licenses (
    license_id varchar(10),
    first_name varchar(50),
    last_name varchar(50),
    CONSTRAINT licenses_key PRIMARY KEY (license_id)
);

CREATE TABLE registrations (
    registration_id varchar(10),
    registration_date date,
    -- new keyword: REFERENCES
    license_id varchar(10) REFERENCES licenses (license_id),
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

Referential Integrity Implications

1. It affects the order we insert data. We cannot add data to a table that contains a foreign key before the other table referenced by the key has the related records, or we’ll get an error.

2. The reverse applies when we delete data. To maintain referential integrity, the foreign key constraint prevents us from deleting a row from licenses before removing any related rows in registrations, because doing so would leave an orphaned record.

CASCADE: Automatically Deleting (or Updating) Related Records

CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted.

This is more efficient as it allows to work ‘forwards’, ie. deleting the referenced row cascades over all referencing rows, versus having to deleted all referencing rows first and work ‘backwards’.

CREATE TABLE registrations (
    registration_id varchar(10),
    registration_date date,
    license_id varchar(10) REFERENCES licenses (license_id) ON DELETE CASCADE,
    CONSTRAINT registration_key PRIMARY KEY (registration_id, license_id)
);

NOTE: Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).

Creating a database

CREATE DATABASE analysis;

Creating and removing tables

-- ex1
CREATE TABLE teachers (
    id bigserial,  -- column names are followed by data types
    first_name varchar(25),
    last_name varchar(50),
    school varchar(50),
    hire_date date,
    salary numeric
);

-- ex2
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

-- removing a table
DROP TABLE tablename;

Data Manipulation (CRUD)

Moving data from/to files

COPY table_name
FROM '/home/path/your_file.csv'
WITH (FORMAT CSV, HEADER);

NOTE: if you are using Postgres from a Docker container, make sure the path refers to the relative path within the container. NOTE2: if you wish to export from a table to a file, change the FROM for a TO.

Querying a Table (Read)

To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into:

• a select list (the part that lists the columns to be returned),
• a table list (the part that lists the tables from which to retrieve the data), and
• an optional qualification (the part that specifies any restrictions).

-- retrieves all columns and all rows (no conditions specified)
SELECT * FROM weather;

-- querying specific columns
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

-- adding a condition
SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

Inserting Data (Create)

When a table is created, it contains no data. Data is conceptually inserted one row at a time. By default, the data values in the records to be inserted should be listed in the order in which the columns appear in the table, separated by commas and between parenthesis:

INSERT INTO products VALUES (1, 'Cheese', 9.99);

Alternatively, column ordering can also be specified:

-- both queries result in the same record being inserted
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

If you don’t have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values. For example:

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');

Inserting multiple records in the same query

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
       ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000),
       ('Samuel', 'Cole', 'Myers Middle School', '2005-08-01', 43500),
       ('Samantha', 'Bush', 'Myers Middle School', '2011-10-30', 36200),
       ('Betty', 'Diaz', 'Myers Middle School', '2005-08-30', 43500),
       ('Kathleen', 'Roush', 'F.D. Roosevelt HS', '2010-10-22', 38500);

Updating Data

The modification of data that is already in the database is referred to as updating. You can update individual rows, all the rows in a table, or a subset of all rows. Each column can be updated separately; the other columns are not affected.

To update existing rows, use the UPDATE command. This requires three pieces of information:

1. The name of the table and column to update
2. The new value of the column
3. Which row(s) to update

In general SQL does not provide a unique identifier for rows. Instead, you specify which conditions a row must meet in order to be updated. Only if you have a primary key in the table (independent of whether you declared it or not) can you reliably address individual rows by choosing a condition that matches the primary key.

For example, this command updates all products that have a price of 5 to have a price of 10:

UPDATE products SET price = 10 WHERE price = 5;

First is the key word UPDATE followed by the table name. As usual, the table name can be schema-qualified, otherwise it is looked up in the path. Next is the key word SET followed by the column name, an equal sign, and the new column value. The new column value can be any scalar expression, not just a constant. For example, if you want to raise the price of all products by 10% you could use:

-- this query affects all rows of the price column
UPDATE products SET price = price * 1.10;

You can update more than one column in an UPDATE command by listing more than one assignment in the SET clause. For example:

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

Deleting Data

Just as adding data is only possible in whole rows, you can only remove entire rows from a table. SQL does not provide a way to directly address individual rows. Therefore, removing rows can only be done by specifying conditions that the rows to be removed have to match. If you have a primary key in the table then you can specify the exact row. But you can also remove groups of rows matching a condition, or you can remove all rows in the table at once.

You use the DELETE command to remove rows; the syntax is very similar to the UPDATE command. For instance, to remove all rows from the products table that have a price of 10, use:

DELETE FROM products WHERE price = 10;

-- Warning! The below query deletes ALL the rows in the table
DELETE FROM products;

Common Table Expressions ¹

Always favor CTEs.

Example

WITH provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query. Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

WITH regional_sales AS (
   SELECT region, SUM(amount) AS total_sales
   FROM orders
   GROUP BY region
), top_regions AS (
   SELECT region
   FROM regional_sales
   WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
      product,
      SUM(quantity) AS product_units,
      SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Which displays per-product sales totals in only the top sales regions. The WITH clause defines two auxiliary statements named regional_sales and top_regions, where the output of regional_sales is used in top_regions and the output of top_regions is used in the primary SELECT query. This example could have been written without WITH, but we’d have needed two levels of nested sub-SELECTs. It’s a bit easier to follow this way.

Data Types

Overview

• Primitives: Integer, Numeric, String, Boolean
• Structured: Date/Time, Array, Range, UUID
• Document: JSON/JSONB, XML, Key-value (Hstore)
• Geometry: Point, Line, Circle, Polygon
• Customizations: Composite, Custom Types

NULLs

If no default value is declared explicitly, the default value is the NULL value. This usually makes sense because a NULL value can be considered to represent unknown data. A NULL is distinctively different from a 0 or "". A NULL represents absence of evidence—and not evidence of absence.

Numeric Types

Indexes

Concept and Purpose

Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.

After an index is created, the system has to keep it synchronized with the table. This adds overhead to data manipulation operations. Therefore indexes that are seldom or never used in queries should be removed. It is the task of the database programmer to foresee which indexes will be useful.

Indexes are special tables that, unlike normal data tables, are kept in a specific order. Instead of containing all of the data about an entity, however, an index contains only the column (or columns) used to locate rows in the data table, along with a pointer that indicates where the rows are physically located. Therefore, the role of indexes is to facilitate the retrieval of a subset of a table’s rows and columns without the need to inspect every row in the table.

Indexes can also benefit UPDATE and DELETE commands with search conditions. Indexes can moreover be used in join searches. Thus, an index defined on a column that is part of a join condition can also significantly speed up queries with joins.

Once an index is created, no further intervention is required: the system will update the index when the table is modified, and it will use the index in queries when it thinks doing so would be more efficient than a sequential table scan. But you might have to run the ANALYZE command regularly to update statistics to allow the query planner to make educated decisions.

Motivating Example

Assume the following table:

CREATE TABLE test1 (
    id integer,
    content varchar
);

In this table, assume one of the most popular queries is the following:

SELECT content FROM test1 WHERE id = constant;

With no advance preparation, every time this type of query runs the system would have to scan the entire test1 table, row by row, to find all matching entries—ie. the search would take place in linear time. Whereas with an index, it might only have to walk a few levels deep into a search tree.

Creating and Removing an Index

Indexes can be added to and removed from tables at any time:

-- creating an index on the id column
CREATE INDEX test1_id_index ON test1 (id);

-- dropping an index
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

B-trees

By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: < <= = >= >

Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. Also, an IS NULL or IS NOT NULL condition on an index column can be used with a B-tree index.

Multi-column Indexes

An index can be defined on more than one column of a table. For example, if you have a table of this form:

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

And you frequently issue queries like:

SELECT name FROM test2 WHERE major = constant AND minor = constant;

Then it might be appropriate to define an index on the columns major and minor together, e.g.:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned.

For example, given an index on (a, b, c) and a query condition WHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry with a = 5 and b = 42 up through the last entry with a = 5. Index entries with c >= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints on b and/or c with no constraint on a — but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.

NOTE: Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.

Relationships

One-to-One Relationship

There is only one match for an id in each of the two tables. There are no duplicate id values in either table: only one row in the left table exists with an id of 1, and only one row in the right table has an id of 1. Eg. Each US state is managed by one governor; and each governor is in charge of one (the same) state.

One-to-Many Relationship

A key value in the first table will have multiple matching values in the second table’s joined column. Eg. Each US state contains multiple counties.

Many-to-Many Relationship

Multiple rows in the first table will have multiple matching rows in the second table. Eg. Students and classes: a student can register for many classes, and a class can include many students.

Table Joins

When you join tables in a query, the database connects rows in both tables where the columns you specified for the join have matching values. The query results then include columns from both tables if you requested them as part of the query.

Consider the two following tables:

Inner Join

We use JOIN when we want to return rows that have a match in the columns following the ON keyword. Only rows that match in both tables are kept.

SELECT *
-- JOIN and INNER JOIN are equivalent
FROM companies_left cl JOIN companies_right cr
ON cl.id = cr.id;

Produces:

NOTE: Watchout for this type of join as there may be unintended loss of data (eg. we may want to keep data from the left table even though it has no reference on the right table, for such a case, see LEFT JOIN).

Left Join and Right Join

In contrast to JOIN, the LEFT JOIN and RIGHT JOIN keywords each return all rows from one table (left table if LEFT JOIN; right table if RIGHT JOIN) and display blank rows from the other table if no matching values are found in the joined columns.

SELECT *
FROM companies_left cl
LEFT OUTER JOIN companies_right cr
ON (cl.id = cr.id);

Produces:

Full Outer Join

When you want to see all rows from both tables in a join, regardless of whether any match, use the FULL OUTER JOIN option. A full outer join is admittedly less useful and used less often than inner and left or right joins. Still, you can use it for a couple of tasks: to merge two data sources that partially overlap or to visualize the degree to which the tables share matching values.

SELECT *
FROM companies_left cl
FULL OUTER JOIN companies_right cr
ON (cl.id = cr.id);

Produces:

Cross Join

In a CROSS JOIN query, the result (also known as a Cartesian product) lines up each row in the left table with each row in the right table to present all possible combinations of rows. Because the join doesn’t need to find matches between key fields, there’s no need to provide the clause using the ON keyword.

SELECT *
FROM companies_left
CROSS JOIN companies_right;

Produces:

JSON

Semantics

• Dot . is used for member access
• Square brackets [] are used for array access
• SQL/JSON arrays are 0-based indexed (unlike regular SQL arrays that start from 1)

Operators²

Examples

Transactions

Concept

The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Highlights

• A transaction is said to be atomic: from the point of view of other transactions, it either happens completely or not at all.

• A transactional database guarantees that all the updates made by a transaction are logged in permanent storage (i.e., on disk) before the transaction is reported complete.

• The updates made so far by an open transaction are invisible to other transactions until the transaction completes, whereupon all the updates become visible simultaneously.

Code

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
COMMIT;

NOTE: PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

Savepoints and Rollbacks

Savepoints allow you to selectively discard parts of the transaction, while committing the rest. After defining a savepoint with SAVEPOINT, you can if needed roll back to the savepoint with ROLLBACK TO. All the transaction’s database changes between defining the savepoint and rolling back to it are discarded, but changes earlier than the savepoint are kept. After rolling back to a savepoint, it continues to be defined, so you can roll back to it several times.

Rollback—Trivial Example

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally';
COMMIT;

Window Functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities and they all are displayed in the query’s output.

Example: Sales Orders Table

Let’s analyze (artificial) sales orders data (Table 1). The table has the following attributes:

• order_id: the id of the sales order
• sales_value_thsd: the total amount of the sales order, in thousands of dollars
• point_of_sale: represents the store where the sale took place
• date: date of the transaction, YYYY-MM-DD

Table0. Sales Orders Table

Query1: Running Sum/Total Across All Orders by Date (No Partitions)

--running sum across all orders by date
SELECT date, sales_value_thsd, SUM(sales_value_thsd) OVER
 (ORDER BY date ASC ROWS UNBOUNDED PRECEDING) as running_sum
    -- UNBOUNDED PRECEDING is the default
FROM sales_orders;

Table1. Running Sum/Total without Partitions

Query2: Ranking Largest Orders Partitioning by point_of_sale

--ranking largest orders by value_thsd partitioning by point_of_sale
SELECT point_of_sale, order_id, sales_value_thsd, RANK() OVER
 (PARTITION BY point_of_sale ORDER BY sales_value_thsd DESC)
FROM sales_orders;

Table2. Value Rank by Partition (descending)

As shown here, the RANK function produces a numerical rank for each distinct ORDER BY value in the current row’s partition, using the order defined by the ORDER BY clause. rank needs no explicit parameter, because its behavior is entirely determined by the OVER clause.

The rows considered by a window function are those of the “virtual table” produced by the query’s FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.

Query3: Average Sales Partitioning by point_of_sale

--average sales_value_thsd by point of sale
SELECT point_of_sale, sales_value_thsd, round(avg_sales_thsd, 2) AS pos_avg
FROM (
    SELECT point_of_sale, sales_value_thsd, avg(sales_value_thsd)  OVER
        (partition by point_of_sale) as avg_sales_thsd
    FROM sales_orders
) as subq;

Table3. Average Sales by Partition

Query4: Average Sales and RunningSum Partitioning by point_of_sale

--average and running sum of sales by point_of_sale
SELECT
 point_of_sale,
 date,
 order_id,
 sales_value_thsd,
 round(running_sum, 2) AS running_sum,
 round(running_avg, 2) AS running_avg
FROM (
 SELECT
  point_of_sale,
  date,
  order_id,
  sales_value_thsd,
  sum(sales_value_thsd) OVER w AS running_sum,
  avg(sales_value_thsd) OVER w AS running_avg
 FROM sales_orders
 WINDOW w AS (PARTITION BY point_of_sale ORDER BY date ASC)
) AS subq;

Table4. Two Window Functions (sum and avg) Over the Same Window

Window Function Terminology

A window function call always contains an OVER clause directly following the window function’s name and argument(s). This is what syntactically distinguishes it from a normal function or non-window aggregate. The OVER clause determines exactly how the rows of the query are split up for processing by the window function.

The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.

You can also control the order in which rows are processed by window functions using ORDER BY within OVER (the window ORDER BY does not even have to match the order in which the rows are output).

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after non-window aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa. If there is a need to filter or group rows after the window calculations are performed, you can use a sub-select (see Query3, which uses a sub-select to execute a window function and an outer select to round it).

The frame_clause specifies the set of rows constituting the window frame, which is a subset of the current partition, for those window functions that act on the frame instead of the whole partition. The set of rows in the frame can vary depending on which row is the current row. The frame can be specified in RANGE, ROWS or GROUPS mode; in each case, it runs from the frame_start to the frame_end. If frame_end is omitted, the end defaults to CURRENT ROW.

A frame_start of UNBOUNDED PRECEDING means that the frame starts with the first row of the partition, and similarly a frame_end of UNBOUNDED FOLLOWING means that the frame ends with the last row of the partition. The default framing option is RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Where BETWEEN is not specified, the frame is implicitly bounded by the current row.

With ORDER BY, this sets the frame to be all rows from the partition start up through the current row’s last ORDER BY peer. Without ORDER BY, this means all rows of the partition are included in the window frame, since all rows become peers of the current row.

Window Function Processing

When multiple window functions are used, all the window functions having syntactically equivalent PARTITION BY and ORDER BY clauses in their window definitions are guaranteed to be evaluated in a single pass over the data. Therefore they will see the same sort ordering, even if the ORDER BY does not uniquely determine an ordering. However, no guarantees are made about the evaluation of functions having different PARTITION BY or ORDER BY specifications. (In such cases a sort step is typically required between the passes of window function evaluations, and the sort is not guaranteed to preserve ordering of rows that its ORDER BY sees as equivalent.)

Currently, window functions always require presorted data, and so the query output will be ordered according to one or another of the window functions’ PARTITION BY/ORDER BY clauses. It is not recommended to rely on this, however. Use an explicit top-level ORDER BY clause if you want to be sure the results are sorted in a particular way.

Table of General-Purpose Window Functions

Docs

• PostgreSQL 14 Docs - Chapter 3.5 – Window Functions
• PostgreSQL 14 Docs - Chapter 4.2.8 – Window Function Calls
• PostgreSQL 14 Docs - Chapter 7.2 – Window Function Processing
• PostgreSQL 14 Docs - Chapter 9.22 – [List] of Window Functions