MAX Function in PostgreSQL
The PostgreSQL MAX aggregate function returns the largest value from a set of input values. This function extracts one value from numerous input rows. It can handle numeric, text, date/time, enum, inet, interval, money, oid, pg_lsn, tid, xid8, and arrays of these types. SELECT max(temp_lo) FROM weather; finds the highest low-temperature reading from a weather table.
Importantly, MAX cannot be used directly in a WHERE clause since it filters input rows before aggregate computations. The subquery SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); finds records where a column’s value meets the overall maximum. To find the greatest value in distinct rows, the MAX function is often used with the GROUP BY clause. To discover each city’s highest low temperature, use SELECT city, max(temp_lo) FROM weather GROUP BY city;.
Using MAX in the HAVING clause helps filter aggregated groups, such as identify city, count(), max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40; to identify cities with temperatures below 40. PostgreSQL FILTER clause allows for conditional inclusion of rows in aggregate calculations, similar to a WHERE clause. For example, SELECT city, count() FILTER (WHERE temp_lo < 45), max(temp_lo) FROM weather GROUP BY city;.
Basic Syntax and Usage
- Basic method use MAX in a SELECT query to find the highest column value across all table rows.
- Using a weather table, for instance, you would write the following to determine the lowest temperature reading: Choose maximum (temp_lo) from the weather;
- Data types that can use the MAX function include numeric, text, date/time, and enum types; arrays of these types; and inet, interval, money, oid, pg_lsn, tid, and xid8.
Interaction with SQL Clauses
WHERE Clause: Knowing that the MAX function cannot be used in a WHERE clause is critical. Since the WHERE clause, which chooses rows for aggregate computation, is logically evaluated before groups and aggregates, this constraint applies. You can acquire the desired result with a subquery. The aggregate computation can be done individually, and the outer query’s WHERE clause can use the result. For instance, a subquery could identify which city has the highest low-temperature reading: WITER-BASED CITY Place temp_lo (SELECT max(temp_lo) FROM weather);
GROUP BY Clause: MAX and the GROUP BY clause are commonly used together. You can use the MAX method to return the maximum value for each individual row collection. Example: Finding each city’s lowest recorded temperature From weather group by city, choose city, max(temp_lo);
HAVING Clause: For aggregate function-based row filtering in PostgreSQL, the HAVING clause is essential. The HAVING clause filters entries after the GROUP BY clause groups them and aggregate functions compute their results, unlike the WHERE clause. This is crucial: aggregate functions are specifically banned in the WHERE clause since they logically evaluate before aggregates are computed, indicating which rows are inputs.
Like a WHERE clause, the FILTER clause selects input rows for an aggregate function. It filters records before aggregation.
Code Example:
CREATE TABLE weather (
city TEXT,
temp_lo INT,
temp_hi INT,
prcp REAL,
date DATE
);
INSERT INTO weather VALUES
('Mumbai',25,33,1.2,'2025-08-30'),
('Delhi',20,36,0.0,'2025-08-30'),
('Chennai',27,35,0.5,'2025-08-30'),
('Delhi',22,34,0.1,'2025-08-31'),
('Mumbai',26,32,2.0,'2025-08-31'),
('Chennai',28,34,0.3,'2025-08-31');
SELECT city,temp_lo
FROM weather
WHERE temp_lo=(SELECT MAX(temp_lo) FROM weather);
SELECT city,MAX(temp_lo) AS max_lo
FROM weather
GROUP BY city;
SELECT city,MAX(temp_lo) AS max_lo
FROM weather
GROUP BY city
HAVING MAX(temp_lo)<27;
SELECT city,
COUNT(*) FILTER(WHERE temp_lo<25) AS days_below_25,
MAX(temp_lo) AS max_lo
FROM weather
GROUP BY city;Output:
CREATE TABLE
INSERT 0 6
city | temp_lo
---------+---------
Chennai | 28
(1 row)
city | max_lo
---------+--------
Mumbai | 26
Delhi | 22
Chennai | 28
(3 rows)
city | max_lo
--------+--------
Mumbai | 26
Delhi | 22
(2 rows)
city | days_below_25 | max_lo
---------+---------------+--------
Mumbai | 0 | 26
Delhi | 2 | 22
Chennai | 0 | 28
(3 rows)NULL Value Handling
MAX() and most aggregate methods disregard NULL for computation. If a column has exclusively NULL values, MAX returns NULL. NULL is ignored, therefore 3 and NULL AVG() is 3. “Unknown” or “not applicable” are common interpretations of NULL, a special value in PostgreSQL that denotes the lack of any value. This idea is essential to relational databases because it sets it apart from actual values like zero or an empty string. Because NULL = NULL evaluates to NULL, predicates in relational databases that use three-valued logic (3VL) where truth values might be true, false, or unknown (NULL) help eliminate ambiguity.
When given NULL as an argument, the majority of operators return NULL. For example, if either an or b are NULL, comparisons such as a > b or a = b will not return any records since the comparison’s result is NULL (unknown), and WHERE clauses only filter rows when the condition is true. PostgreSQL offers two special operators, IS NULL and IS NOT NULL, to specifically check for NULL data. IS NOT DISTINCT FROM and IS DISTINCT FROM are used to check equivalency when accounting for NULLs; NULL IS DISTINCT FROM NULL returns false, whereas NULL IS NOT DISTINCT FROM NULL returns true.
MAX as a Window Function
MAX can also be a window function. A window function aggregates table rows relevant to the current row while keeping their identities. Built-in or user-created aggregate functions can be window functions. When used as a window function, the OVER clause defines the “window” of rows the function runs on after MAX.Window function divides OVER()’s PARTITION BY clause individually.
ORDER BY orders window function partition row processing. An ORDER BY clause for an aggregate window function like SUM with the default window frame (or left off) aggregates from the partition start to the current row, resulting in a “running sum” behaviour. Only the ORDER BY clause and SELECT list can use window functions. Due to their logic, GROUP BY, HAVING, and WHERE clauses prohibit them.
Conclusion
The versatile aggregate and window function PostgreSQL MAX() finds the highest value in a dataset or grouped partitions. Numeric, text, date/time, enum, inet, money, and array data types are supported. Due to SQL’s logical evaluation order, MAX cannot be used directly in a WHERE clause, although subqueries or HAVING clauses can filter after aggregation. MAX efficiently returns per-group maximums with GROUP BY and provides row-level insights without collapsing rows like a window function. MAX provides precise and flexible query capabilities for simple lookups and complex analytical activities by disregarding NULL values and allowing advanced clauses like FILTER.