CASE in PL/pgSQL in PostgreSQL - Time & Space Complexity

Time Complexity: CASE in PL/pgSQL

O(n)

Understanding Time Complexity

We want to understand how the time it takes to run a CASE statement in PL/pgSQL changes as the number of conditions grows.

How does adding more conditions affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of the following PL/pgSQL CASE statement.

DECLARE
  grade CHAR := 'B';
  result TEXT;
BEGIN
  CASE grade
    WHEN 'A' THEN result := 'Excellent';
    WHEN 'B' THEN result := 'Good';
    WHEN 'C' THEN result := 'Average';
    WHEN 'D' THEN result := 'Below Average';
    ELSE result := 'Fail';
  END CASE;
END;
    

This code checks the value of grade and sets result based on matching conditions.

Identify Repeating Operations

Look for repeated checks or comparisons inside the CASE.

• Primary operation: Comparing the input value to each WHEN condition.
• How many times: Up to the number of WHEN conditions, until a match is found.

How Execution Grows With Input

As the number of WHEN conditions increases, the number of comparisons can grow.

Input Size (number of WHENs)	Approx. Comparisons
5	Up to 5
50	Up to 50
500	Up to 500

Pattern observation: The number of comparisons grows roughly in a straight line with the number of conditions.

Final Time Complexity

Time Complexity: O(n)

This means the time to find a match grows linearly with the number of CASE conditions.

Common Mistake

[X] Wrong: "The CASE statement always runs in constant time no matter how many conditions there are."

[OK] Correct: The database checks conditions one by one until it finds a match, so more conditions mean more checks and more time.

Interview Connect

Understanding how CASE statements scale helps you write efficient conditional logic in database functions, a useful skill in many real projects.

Self-Check

"What if we replaced the CASE with a lookup table join? How would the time complexity change?"