GREATEST and LEAST functions in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GREATEST with NULL values

What is the output of this PostgreSQL query?

SELECT GREATEST(5, NULL, 3);

PostgreSQL

SELECT GREATEST(5, NULL, 3);

A Error

B 5

C 3

D NULL

💡 Hint

Remember how GREATEST handles NULL values in PostgreSQL.

In PostgreSQL, GREATEST returns NULL if any argument is NULL unless you explicitly handle NULLs.

❓ query_result

intermediate

LEAST function with mixed data types

What is the result of this query?

SELECT LEAST('10', '2', '30');

Note: The values are strings.

PostgreSQL

SELECT LEAST('10', '2', '30');

A '10'

B Error

C '30'

D '2'

💡 Hint

Strings are compared lexicographically in PostgreSQL.

LEAST compares strings lexicographically. '10' < '2' < '30' because the first characters '1' < '2' < '3', so '10' is the smallest.

📝 Syntax

advanced

Identify the syntax error in GREATEST usage

Which option contains a syntax error when using the GREATEST function?

A SELECT GREATEST(1, 2, 3);

B SELECT GREATEST(5, 10);

C SELECT GREATEST(1, 2,);

D SELECT GREATEST(7);

💡 Hint

Check for trailing commas in function arguments.

Option C has a trailing comma after 2, which is a syntax error in PostgreSQL function calls.

❓ optimization

advanced

Optimizing LEAST function with many columns

You want to find the smallest value among 10 numeric columns in a large table. Which approach is most efficient?

A Use LEAST(col1, col2, ..., col10) in SELECT clause directly.

B Use a CASE expression comparing each column pairwise.

C Unpivot columns into rows and use MIN() aggregation.

D Create a user-defined function to loop through columns.

💡 Hint

Consider built-in function efficiency versus complex expressions.

LEAST is optimized internally and faster than manual CASE or unpivoting for this use case.

🧠 Conceptual

expert

Behavior of GREATEST with mixed NULL and NOT NULL columns

Given a table with columns a, b, c where b can be NULL, what does this query return?

SELECT GREATEST(a, b, c) FROM table WHERE id = 1;

Assuming a=5, b=NULL, c=3 for id=1.

PostgreSQL

SELECT GREATEST(a, b, c) FROM table WHERE id = 1;

A 3

B NULL

C 5

D Error

💡 Hint

How does GREATEST handle NULL values in columns?

GREATEST returns NULL if any argument is NULL, so the presence of b=NULL causes the result to be NULL.