Shape / columns · All operations

Round or limit numbers

A variant of Apply a maths function

Round to decimal places or significant figures, round up or down, or clip values to a lower and upper bound.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Round to places and significant figures, round up and down, and clip to bounds

SQL
CREATE TEMP TABLE nums AS SELECT * FROM (VALUES
  (1, 1234.5678::DOUBLE), (2, 0.00123456::DOUBLE), (3, 0.0::DOUBLE), (4, -15.26::DOUBLE), (5, 2.5::DOUBLE), (6, NULL::DOUBLE)
) t(id, x);
-- round() takes its places as a constant, so significant figures are written with the g format; clipping is a CASE because
-- greatest() and least() skip a NULL and would turn a missing number into a bound
CREATE TEMP TABLE result AS
SELECT id, round(x, 2) AS places, round(x, -2) AS hundreds, round(x) AS whole, format('{:.3g}', x)::DOUBLE AS three_sig,
       ceil(x) AS up, floor(x) AS down, CASE WHEN x < 0 THEN 0.0 WHEN x > 100 THEN 100.0 ELSE x END AS clipped
FROM nums;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT [places, hundreds, whole, three_sig, up, down, clipped] FROM result WHERE id = 1) = [1234.57, 1200.0, 1235.0, 1230.0, 1235.0, 1234.0, 100.0] AND (SELECT [places, three_sig, clipped] FROM result WHERE id = 2) = [0.0, 0.00123, 0.00123456] AND (SELECT [three_sig, clipped] FROM result WHERE id = 3) = [0.0, 0.0] AND (SELECT [places, whole, three_sig, up, down, clipped] FROM result WHERE id = 4) = [-15.26, -15.0, -15.3, -15.0, -16.0, 0.0] AND (SELECT [whole, up, down, clipped] FROM result WHERE id = 5) = [3.0, 3.0, 2.0, 2.5] AND (SELECT count(*) FROM result WHERE id = 6 AND places IS NULL AND three_sig IS NULL AND clipped IS NULL) = 1) THEN 'as expected' ELSE error('the numbers were not rounded to places and three significant figures, rounded up and down, and clipped between 0 and 100') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the numbers were not rounded to places and three significant figures, rounded up and down, and clipped between 0 and 100”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Duckle (2)
  • Clampread 2026-09-25

    clip values to a low and high bound

  • Roundread 2026-09-25

    round a number

Ibis (4)
  • Ceilread 2026-09-27

    Round up to the nearest integer

  • Clipread 2026-09-27

    Limit a value to a lower and an upper bound

  • Floorread 2026-09-27

    Round down to the nearest integer

  • Roundread 2026-09-27

    Round to a given number of digits

KNIME Analytics Platform (1)
  • Number Rounderread 2026-09-27

    Round numeric columns to decimal places or significant figures with a chosen rounding mode

Polars (1)

Background

Rounding is the process of adjusting a number to an approximate, more convenient value, often with a shorter or simpler representation.[1] In computer science, clamping, or clipping, is the process of limiting a value to a range between a minimum and a maximum value.[2] Clamping moves a value that is outside the allowed range to the nearest boundary.[2] In DuckDB, round(v, s) rounds to s decimal places, and values of s below zero are allowed.[3]

When to use it

Rounding avoids misleadingly precise reporting: a quantity computed as 123456 but known to be accurate only to within a few hundred units is usually better stated as about 123500.[1] Rounding to significant figures is a more general-purpose technique than rounding to n digits, since it handles numbers of different scales in a uniform way.[4] Round a column whose values differ greatly in size, such as city and country populations, to significant figures rather than to a fixed number of places. (Meridian's view)

Round half to even sends a value whose fractional part is 0.5 to the nearest even integer, so 23.5 and 24.5 both become 24.[1] Round half to even is the default rounding mode used in IEEE 754 operations for results in binary floating-point formats.[1] In DuckDB, roundbankers and its alias round_even round half to even, so roundbankers(24.5, 0) is 24.0.[3] In DuckDB, casting a FLOAT or DOUBLE to an integer rounds ties to the nearest even number, while casting a DECIMAL to an integer rounds ties away from zero.[5] In PostgreSQL, round on numeric breaks ties away from zero, while for double precision the tie-breaking is platform dependent, with round to nearest even the most common rule.[6] Name the tie rule a report needs, and choose the function or type that gives it, rather than trusting a default. (Meridian's view)

Rounding a number twice in succession to coarser precisions is not guaranteed to give the same result as rounding once, except with directed rounding.[1] Rounding 9.46 to the nearest tenth gives 9.5, and then 10 when rounding to the nearest integer with round half to even, but rounding 9.46 directly gives 9.[1] Round once, from the full-precision value, at the point where the number is reported. (Meridian's view)

In PostgreSQL, GREATEST and LEAST ignore NULL arguments and return NULL only if all of them are NULL, a deviation from the SQL standard, under which any NULL argument gives NULL.[7] Clip with a CASE expression, or test for NULL first, wherever a missing value must stay missing rather than come back as a bound. (Meridian's view)

The method

In PostgreSQL, ceil gives the nearest integer greater than or equal to its argument, so ceil(-42.8) is -42.[6] In PostgreSQL, floor gives the nearest integer less than or equal to its argument, so floor(-42.8) is -43.[6] PostgreSQL's trunc truncates towards zero, so trunc(-42.8) is -42.[6] In PostgreSQL, round(v, s) rounds v to s decimal places, and round(1234.56, -1) gives 1230.[6]

To round a number to n significant figures, 1 is added to the n-th digit if the next digit is greater than 5, or is 5 followed by other non-zero digits.[4] When the digit after the n-th significant figure is 5 followed by nothing or only zeros, rounding to significant figures needs a tie-breaking rule.[4] Round half to even is the method preferred by many scientific disciplines, because it avoids skewing the average value of a long list of values upwards.[4]

The round-half-to-even method has served as American Standard Z25.1 and ASTM standard E-29 since 1940.[1] Until the 1980s, the rounding used in floating-point computer arithmetic was usually fixed by the hardware, poorly documented, inconsistent and different for each brand and model of computer.[1]

Sources

  1. [1]Rounding - Wikipediasecondary source
  2. [2]Clamp (function) - Wikipediasecondary source
  3. [3]Numeric Functions – DuckDBdocumentation
  4. [4]Significant figures - Wikipediasecondary source
  5. [5]Numeric Types – DuckDBdocumentation
  6. [6]PostgreSQL: Documentation: 18: 9.3. Mathematical Functions and Operatorsdocumentation
  7. [7]PostgreSQL: Documentation: 18: 9.18. Conditional Expressionsdocumentation