How to Use PARTITION BY in PostgreSQL: Syntax and Examples

In PostgreSQL, PARTITION BY is used inside window functions to divide rows into groups called partitions. Each partition is processed separately, allowing calculations like running totals or ranks within each group.

Syntax

The PARTITION BY clause is used within window functions to split the data into partitions. The general syntax is:

window_function() OVER (PARTITION BY column1, column2 ORDER BY column3)

Here, window_function() can be functions like ROW_NUMBER(), RANK(), SUM(), etc. The PARTITION BY clause groups rows by the specified columns, and ORDER BY defines the order within each partition.

sql

window_function() OVER (PARTITION BY column1, column2 ORDER BY column3)

Example

This example shows how to use PARTITION BY with ROW_NUMBER() to assign a row number to each employee within their department.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary INT
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 4800),
('Charlie', 'HR', 4500),
('David', 'HR', 4700),
('Eve', 'Sales', 5200);

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_department
FROM employees
ORDER BY department, rank_in_department;

Output

 name   | department | salary | rank_in_department 
--------+------------+--------+--------------------
 Eve    | Sales      |   5200 |                  1
 Alice  | Sales      |   5000 |                  2
 Bob    | Sales      |   4800 |                  3
 David  | HR         |   4700 |                  1
 Charlie| HR         |   4500 |                  2

Common Pitfalls

Common mistakes when using PARTITION BY include:

• Forgetting to use ORDER BY inside the window function, which can lead to unpredictable row numbering or aggregation order.
• Using PARTITION BY outside window functions, which is invalid syntax.
• Confusing GROUP BY with PARTITION BY: GROUP BY aggregates rows into one result per group, while PARTITION BY keeps all rows but groups them for window calculations.

Example of wrong and right usage:

sql

-- Wrong: PARTITION BY used outside window function
SELECT name, department, salary
FROM employees;

-- Right: PARTITION BY inside window function
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Quick Reference

Clause	Description	Example
PARTITION BY	Divides rows into groups for window functions	PARTITION BY department
ORDER BY	Defines order of rows within each partition	ORDER BY salary DESC
Window Functions	Functions that operate over partitions	ROW_NUMBER(), RANK(), SUM()

Key Takeaways

Use PARTITION BY inside window functions to group rows without collapsing them.

Always pair PARTITION BY with ORDER BY for predictable results in ranking or running totals.

PARTITION BY is not a replacement for GROUP BY; it keeps all rows and adds calculations per group.

Common errors include using PARTITION BY outside window functions or omitting ORDER BY.

Window functions with PARTITION BY help analyze data within groups efficiently.