ROUND

Reference material for ROUND function

Rounds a value to a specified number of decimal places.

Syntax

ROUND(<value> [, <decimal>])

Parameters

Parameter	Description	Supported input types
<value>	The value to be rounded
<decimal>	Optional. An INTEGER constant that defines the decimal range of the returned value. By default, ROUND returns whole numbers.	INTEGER

Return Types

DOUBLE PRECISION

Rounding behavior

When rounding values that are exactly halfway between two possible results (for example, 1.5 or 2.5 when rounding to the nearest integer), ROUND uses different strategies depending on the data type:

• DOUBLE PRECISION: Uses banker’s rounding (also called round half to even). Ties are rounded to the nearest even number. For example, both 1.5 and 2.5 round to 2 (the nearest even number).
• NUMERIC: Uses standard rounding (round half up). Ties are always rounded up. For example, 1.5 rounds to 2 and 2.5 rounds to 3.

This behavior ensures that when working with floating point numbers, rounding errors don’t accumulate in a particular direction over many operations.

Example

The following example returns the rounded value of 5.4. Since there is no specification of the decimal range, the functions returns a whole number:

SELECT ROUND(5.4);

round double
5

Rows: 1Execution time: 7.16ms

The following example rounds the value 5.6930 to 1 decimal place:

SELECT ROUND(5.6930, 1);

round double
5.7

Rows: 1Execution time: 5.46ms

The following examples demonstrate the tie-breaking behavior for values exactly halfway between two results:

SELECT ROUND(1.5), ROUND(2.5);

round double	round double
2	2

Rows: 1Execution time: 5.73ms

Notice how both 1.5 and 2.5 round to 2 (the nearest even number). This is banker’s rounding behavior for DOUBLE PRECISION types.

With NUMERIC types, ties round up instead:

SELECT ROUND('1.5'::NUMERIC), ROUND('2.5'::NUMERIC);

round Decimal(38, 9)	round Decimal(38, 9)
2.0	3.0

Rows: 1Execution time: 5.45ms

The same tie-breaking behavior applies when rounding to a specific number of decimal places:

SELECT ROUND(1.15, 1), ROUND(1.25, 1);

round double	round double
1.2	1.2

Rows: 1Execution time: 5.43ms

Both 1.15 and 1.25 round to 1.2 when rounding to 1 decimal place, demonstrating banker’s rounding.

With NUMERIC types at a specific decimal precision:

SELECT ROUND('1.15'::NUMERIC, 1), ROUND('1.25'::NUMERIC, 1);

round Decimal(38, 9)	round Decimal(38, 9)
1.2	1.3

Rows: 1Execution time: 10.61ms