Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL - Step-by-Step Execution

Concept Flow - Mathematical functions (ROUND, CEIL, FLOOR, ABS)

Input Number

↓

Apply Function Logic

↓

Return Result Number

The input number goes through the chosen mathematical function, which processes it and returns the transformed number.

Execution Sample

PostgreSQL

SELECT ROUND(4.7), CEIL(4.2), FLOOR(4.8), ABS(-5);

This query applies ROUND, CEIL, FLOOR, and ABS functions to given numbers and returns their results.

Execution Table

Step	Function	Input	Operation	Result
1	ROUND	4.7	Round to nearest integer	5
2	CEIL	4.2	Round up to next integer	5
3	FLOOR	4.8	Round down to previous integer	4
4	ABS	-5	Absolute value (remove sign)	5
5	END		All functions applied	Query returns (5,5,4,5)

💡 All functions processed inputs and returned their respective results.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
ROUND_result	NULL	5	5	5	5	5
CEIL_result	NULL	NULL	5	5	5	5
FLOOR_result	NULL	NULL	NULL	4	4	4
ABS_result	NULL	NULL	NULL	NULL	5	5

Key Moments - 3 Insights

Why does ROUND(4.7) return 5 instead of 4?

ROUND rounds to the nearest integer. Since 4.7 is closer to 5 than 4, it returns 5 as shown in step 1 of the execution_table.

What is the difference between CEIL and FLOOR functions?

CEIL always rounds up to the next integer (step 2), while FLOOR always rounds down to the previous integer (step 3), even if the number is not an integer.

Why does ABS(-5) return 5?

ABS returns the absolute value, which means it removes the negative sign. So ABS(-5) becomes 5 as shown in step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of FLOOR(4.8) at step 3?

A 4.8

B 4

C 5

D 3

💡 Hint

Check the 'Result' column in row with Step 3 for FLOOR function.

At which step does the ABS function process its input?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look for the row where Function is ABS in the execution_table.

If ROUND(4.3) was used instead of ROUND(4.7), what would be the result at step 1?

A 4

B 5

C 3

D 4.3

💡 Hint

ROUND rounds to nearest integer; check how 4.3 compares to 4 and 5.

Concept Snapshot

Mathematical functions in PostgreSQL:
- ROUND(x): rounds x to nearest integer
- CEIL(x): rounds x up to next integer
- FLOOR(x): rounds x down to previous integer
- ABS(x): returns absolute value of x
Use these to transform numbers easily in queries.

Full Transcript

This visual execution trace shows how PostgreSQL mathematical functions ROUND, CEIL, FLOOR, and ABS work step-by-step. The input number is processed by each function: ROUND rounds to the nearest integer, CEIL rounds up, FLOOR rounds down, and ABS returns the positive value. The execution table lists each step with input, operation, and result. Variable tracker shows how results build up. Key moments clarify common confusions like rounding behavior and absolute value. The quiz tests understanding by referencing the execution steps. This helps beginners see exactly how these functions transform numbers in SQL queries.

Practice

1. What does the ABS function do in PostgreSQL?

easy

A. Rounds a number to the nearest integer

B. Finds the largest integer less than or equal to a number

C. Finds the smallest integer greater than or equal to a number

D. Returns the positive value of a number

Solution

1. Step 1: Understand the ABS function

   The ABS function returns the absolute value, which means it converts any number to its positive form.

2. Step 2: Compare with other functions

   ROUND changes decimals, CEIL and FLOOR deal with integers, so they do not return absolute values.

3. Final Answer:

   Returns the positive value of a number -> Option D

4. Quick Check:

   ABS(-5) = 5 [OK]

Hint: ABS always returns a positive number or zero [OK]

Common Mistakes:

• Confusing ABS with ROUND
• Thinking ABS changes decimal places
• Mixing ABS with CEIL or FLOOR

2. Which of the following is the correct syntax to round the number 3.14159 to 2 decimal places in PostgreSQL?

easy

A. ROUND(3.14159, 2)

B. ROUND(3.14159.2)

C. ROUND(3.14159 TO 2)

D. ROUND(3.14159, '2')

Solution

1. Step 1: Recall ROUND function syntax

   ROUND takes two arguments: the number and the number of decimal places as an integer.

2. Step 2: Check each option

   ROUND(3.14159, 2) uses correct syntax ROUND(number, decimals). Others have syntax errors or wrong argument types.

3. Final Answer:

   ROUND(3.14159, 2) -> Option A

4. Quick Check:

   ROUND(3.14159, 2) = 3.14 [OK]

Hint: ROUND(number, decimals) uses comma and integer decimals [OK]

Common Mistakes:

• Using dot instead of comma between arguments
• Passing decimals as string
• Using 'TO' keyword incorrectly

3. What is the result of this PostgreSQL query?
SELECT CEIL(-3.7), FLOOR(-3.7), ROUND(-3.7), ABS(-3.7);

medium

A. (-3, -4, -3, 3.7)

B. (-4, -3, -4, 3.7)

C. (-3, -4, -4, 3.7)

D. (-3, -4, -4, -3.7)

Solution

1. Step 1: Calculate CEIL and FLOOR for -3.7

   CEIL(-3.7) is the smallest integer >= -3.7, which is -3. FLOOR(-3.7) is the largest integer <= -3.7, which is -4.

2. Step 2: Calculate ROUND and ABS

   ROUND(-3.7) rounds to nearest integer, which is -4. ABS(-3.7) returns positive value 3.7.

3. Final Answer:

   (-3, -4, -4, 3.7) -> Option C

4. Quick Check:

   CEIL(-3.7) = -3, FLOOR(-3.7) = -4, ROUND(-3.7) = -4, ABS(-3.7) = 3.7 [OK]

Hint: CEIL rounds up, FLOOR rounds down, ABS makes positive [OK]

Common Mistakes:

• Mixing CEIL and FLOOR results for negative numbers
• Assuming ROUND always truncates
• Forgetting ABS returns positive

4. Identify the error in this PostgreSQL query:
SELECT ROUND(5.678, '1');

medium

A. ROUND requires only one argument

B. The second argument should be an integer, not a string

C. Missing FROM clause in the query

D. ROUND cannot be used with decimal numbers

Solution

1. Step 1: Check ROUND function arguments

   ROUND expects the second argument to be an integer specifying decimal places, not a string.

2. Step 2: Validate other options

   ROUND works with decimals, FROM clause is optional in SELECT without tables, and ROUND can take two arguments.

3. Final Answer:

   The second argument should be an integer, not a string -> Option B

4. Quick Check:

   ROUND(5.678, 1) is correct, ROUND(5.678, '1') causes error [OK]

Hint: Use integer for decimals, not quoted strings [OK]

Common Mistakes:

• Passing decimals as quoted strings
• Thinking FROM is mandatory for simple SELECT
• Assuming ROUND only takes one argument

5. You want to calculate the total price after applying a 7.5% tax on a price of 123.4567 and round the result to 2 decimal places. Which PostgreSQL expression correctly does this?

hard

A. ROUND(123.4567 * 1.075, 2)

B. CEIL(123.4567 * 1.075, 2)

C. FLOOR(123.4567 * 1.075, 2)

D. ABS(123.4567 * 1.075, 2)

Solution

1. Step 1: Calculate price with tax

   Multiply price by 1.075 (100% + 7.5%) to get total price including tax.

2. Step 2: Round to 2 decimal places

   Use ROUND function with second argument 2 to round to two decimals.

3. Step 3: Check other functions

   CEIL and FLOOR do not accept decimals argument, ABS is for absolute value, so they are incorrect here.

4. Final Answer:

   ROUND(123.4567 * 1.075, 2) -> Option A

5. Quick Check:

   ROUND(123.4567 * 1.075, 2) = 132.72 [OK]

Hint: Use ROUND for decimals, CEIL/FLOOR only for integers [OK]

Common Mistakes:

• Using CEIL or FLOOR with decimal places argument
• Using ABS instead of ROUND for rounding
• Forgetting to multiply by 1.075 for tax