PostgreSQL - Difference between Utility Functions and Aggregate Functions

 

 

🔹 GREATEST(): Function

Returns the largest value from a list of expressions within a row.

✅ Usage:

SELECT GREATEST(10, 20, 5); -- Returns 20

📌 In a query:

SELECT id, math, english, GREATEST(math, english) AS highest_score FROM scores;

Use GREATEST when you're comparing multiple values in the same row.

---

🔹 MAX(): Aggregate Function

Returns the maximum value of a column across multiple rows.

✅ Usage:

SELECT MAX(score) FROM scores;

📌 In a grouped query:

SELECT student_id, MAX(score) AS top_score FROM test_results GROUP BY student_id;

Use MAX when you're looking for the maximum value across rows.

 

 

 

📘 Where is ABSOLUTE used?

ABSOLUTE is used in one specific SQL syntax: positioning cursors in scrollable cursors.

Example:

MOVE ABSOLUTE 3 FROM my_cursor;

This moves the cursor to the third row from the beginning of the result set.

 

🧪 Example: Using MOVE ABSOLUTE with a Cursor

Let’s walk through a step-by-step example using a table and a cursor.

1. Create a sample table:

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

INSERT INTO employees (name)
VALUES 
    ('Alice'),
    ('Bob'),
    ('Charlie'),
    ('Diana'),
    ('Edward');

2. Open a cursor:

BEGIN;

-- Declare a scrollable cursor
DECLARE emp_cursor SCROLL CURSOR FOR
SELECT * FROM employees ORDER BY id;

3. Move the cursor to a specific row using ABSOLUTE:

-- Move to the 3rd row in the result set
MOVE ABSOLUTE 3 FROM emp_cursor;

• This positions the cursor at the 3rd row (Charlie).

You can then fetch the row:

FETCH FROM emp_cursor;

This will return:

 id |  name   
----+---------
  3 | Charlie

4. Close the cursor:

CLOSE emp_cursor;
COMMIT;

📌 Notes:

• MOVE ABSOLUTE n: Moves to the nth row (1-based index). n can be negative to count from the end.

• MOVE ABSOLUTE 0: Positions the cursor before the first row (you'd need to FETCH NEXT after that to get the first row).