When Trailing Zeros Disappear from NUMERIC in YugabyteDB

Sometimes a database difference is not about the value itself, but about how that value is represented when it comes back out.

Here is a simple PostgreSQL compatibility test that can surprise you in YugabyteDB:

				
					CREATE TABLE t (
    id INT PRIMARY KEY,
    v  NUMERIC
);

INSERT INTO t VALUES
(1, 1.50),
(2, 0.1000),
(3, 3.140);

SELECT string_agg(scale(v)::text, ' | ' ORDER BY id)
FROM t;

In PostgreSQL, the result is:

				
					postgres=# SELECT string_agg(scale(v)::text, ' | ' ORDER BY id)
postgres-# FROM t;
 string_agg
------------
 2 | 4 | 3
(1 row)

In YugabyteDB, the result is currently:

				
					yugabyte=# SELECT string_agg(scale(v)::text, ' | ' ORDER BY id)
yugabyte-# FROM t;
 string_agg
------------
 1 | 1 | 2
(1 row)

At first glance, that can look like YugabyteDB changed the numbers.

It did not. The numeric values are still correct. What changed is the trailing-zero scale that PostgreSQL preserves and exposes through functions like scale().

The short version: YugabyteDB currently does not preserve trailing-zero scale for unconstrained NUMERIC values the same way PostgreSQL does. The numeric value remains correct, but functions such as scale() may return different results.

What Is Being Tracked?

This behavior is covered by two related YugabyteDB GitHub issues:

● Issue #29099 tracks the broader behavior where trailing zeros are removed for YSQL numeric types.
● Issue #32283 tracks a focused scale() example where PostgreSQL and YugabyteDB return different scale values for the same inserted NUMERIC literals.

So this is not just a random observation… it is a known PostgreSQL compatibility issue.

The important nuance is that this is not a broken-arithmetic issue. It is a trailing-zero preservation issue.

Reproduce the Difference

Start with an unconstrained NUMERIC column:

				
					DROP TABLE IF EXISTS t;

CREATE TABLE t (
    id INT PRIMARY KEY,
    v  NUMERIC
);

INSERT INTO t VALUES
(1, 1.50),
(2, 0.1000),
(3, 3.140);

Now query the values and their visible scale:

				
					SELECT
    id,
    v,
    scale(v) AS visible_scale
FROM t
ORDER BY id;

YugabyteDB currently returns:

				
					.id |  v   | visible_scale
----+------+---------------
  1 |  1.5 |             1
  2 |  0.1 |             1
  3 | 3.14 |             2

The values are numerically correct:

				
					1.50   is numerically the same as 1.5
0.1000 is numerically the same as 0.1
3.140  is numerically the same as 3.14

But the original trailing zeros are no longer visible when the value is read back.

Why This Happens in YugabyteDB

PostgreSQL and YugabyteDB do not store and retrieve data through the same architecture.

PostgreSQL is a single-node database engine. The SQL layer, storage engine, and type behavior all live inside PostgreSQL.

YugabyteDB is distributed. YSQL provides the PostgreSQL-compatible SQL layer, but table data is persisted through YugabyteDB’s distributed storage layer.

For this specific behavior, the related YugabyteDB issue describes the problem as trailing zeros being removed by the YSQL layer for numeric types.

In practical terms, this means that an unconstrained NUMERIC value can round-trip through YugabyteDB in a normalized form:

				
					Inserted value   Visible value after read
--------------   ------------------------
1.50             1.5
0.1000           0.1
3.140            3.14

The numeric value is preserved.

The trailing-zero scale is not preserved in a way that remains visible to scale().

Compatibility note: PostgreSQL exposes the original fractional scale for these unconstrained NUMERIC values. YugabyteDB currently returns the normalized visible scale. That is why scale(1.50) can behave like scale(1.5) after the value is stored and read back.

Does This Affect Calculations?

For normal arithmetic, this should not change the mathematical result.

You can test that directly:

				
					 SELECT
    id,
    v,
    v = CASE id
          WHEN 1 THEN 1.5
          WHEN 2 THEN 0.1
          WHEN 3 THEN 3.14
        END AS same_numeric_value,
    v + 1 AS plus_one,
    v * 10 AS times_ten
FROM t
ORDER BY id;

Example YugabyteDB output:

				
					.id |  v   | same_numeric_value | plus_one | times_ten
----+------+--------------------+----------+-----------
  1 |  1.5 | t                  |      2.5 |      15.0
  2 |  0.1 | t                  |      1.1 |       1.0
  3 | 3.14 | t                  |     4.14 |     31.40
(3 rows)

The calculations are still based on the numeric value.

So the issue is not:

YugabyteDB stores the wrong number.

The issue is:

YugabyteDB currently does not preserve the same trailing-zero scale metadata that PostgreSQL exposes for unconstrained NUMERIC values.

Where This Can Hurt

Even if calculations are fine, this can still matter.

Area	Possible Impact
`scale(v)` checks	May return different results than PostgreSQL
Migration validation	Text-based comparisons may show false differences
Application formatting	`1.50` may display as `1.5`
Business logic based on entered scale	Risky if `1.5`, `1.50`, and `1.500` mean different things
Normal calculations	Usually not impacted because the numeric value remains correct

Workaround 1: Use a Declared Scale

If scale matters, make it part of the schema.

Instead of this:

				
					v NUMERIC

use this:

				
					v NUMERIC(10,4)

Here is a full demo:

				
					DROP TABLE IF EXISTS t_fixed;

CREATE TABLE t_fixed (
    id INT PRIMARY KEY,
    v  NUMERIC(10,4)
);

INSERT INTO t_fixed VALUES
(1, 1.50),
(2, 0.1000),
(3, 3.140);

SELECT
    id,
    v,
    scale(v) AS visible_scale
FROM t_fixed
ORDER BY id;

YugabyteDB output:

				
					.id |   v    | visible_scale
----+--------+---------------
  1 | 1.5000 |             4
  2 | 0.1000 |             4
  3 | 3.1400 |             4
(3 rows)

That works because the scale is now declared in the column definition.

This is usually the best workaround when scale is part of the data contract.

Good examples:

				
					amount_due      NUMERIC(12,2)
tax_rate        NUMERIC(10,4)
weight_kg       NUMERIC(10,3)
interest_rate   NUMERIC(12,6)

Best workaround: If the number of decimal places has meaning, use NUMERIC(precision, scale). Do not rely on unconstrained NUMERIC to preserve trailing zeros while this compatibility issue remains open.

Workaround 2: Compare Both Behaviors Side by Side

This is a good diagnostic query for migrations.

Create one unconstrained column and one constrained column:

				
					DROP TABLE IF EXISTS t_comparison;

CREATE TABLE t_comparison (
    id INT PRIMARY KEY,
    v_unconstrained NUMERIC,
    v_constrained   NUMERIC(10,4)
);

INSERT INTO t_comparison VALUES
(1, 1.50,   1.50),
(2, 0.1000, 0.1000),
(3, 3.140,  3.140);

SELECT
    id,
    v_unconstrained,
    scale(v_unconstrained) AS scale_unconstrained,
    v_constrained,
    scale(v_constrained) AS scale_constrained
FROM t_comparison
ORDER BY id;

YugabyteDB output:

				
					.id | v_unconstrained | scale_unconstrained | v_constrained | scale_constrained
----+-----------------+---------------------+---------------+-------------------
  1 |             1.5 |                   1 |        1.5000 |                 4
  2 |             0.1 |                   1 |        0.1000 |                 4
  3 |            3.14 |                   2 |        3.1400 |                 4
(3 rows)

This makes the workaround obvious.

The unconstrained column keeps the numeric value, but not the trailing-zero scale.

The constrained column gives you a predictable declared scale.

Workaround 3: Format the Value at Query Time

Sometimes the data model does not require a fixed scale. You only need the value to display a certain way.

In that case, format it when you query it:

				
					SELECT
    id,
    to_char(v, 'FM999999990.0000') AS formatted_v
FROM t
ORDER BY id;

YugabyteDB output:

				
					.id | formatted_v
----+-------------
  1 | 1.5000
  2 | 0.1000
  3 | 3.1400
(3 rows)

This is useful for:

● reports
● exports
● UI display
● demos
● test output normalization

But remember: this is presentation only. It does not restore the original entered scale. It simply formats the numeric value using the pattern you provide.

Workaround 4: Store the Original Scale Separately

There is one more case to think about.

Sometimes the original scale is business data.

For example, maybe these are not equivalent to your application:

They are numerically equal, but the user-entered precision may mean something.

In that case, neither unconstrained NUMERIC nor NUMERIC(10,4) fully solves the problem. You need to store the value and the display scale separately.

				
					DROP TABLE IF EXISTS t_variable_scale;

CREATE TABLE t_variable_scale (
    id            INT PRIMARY KEY,
    v             NUMERIC,
    display_scale INT
);

INSERT INTO t_variable_scale VALUES
(1, 1.50,   2),
(2, 0.1000, 4),
(3, 3.140,  3);

SELECT
    id,
    v,
    display_scale
FROM t_variable_scale
ORDER BY id;

YugabyteDB output:

				
					.id |  v   | display_scale
----+------+---------------
  1 |  1.5 |             2
  2 |  0.1 |             4
  3 | 3.14 |             3
(3 rows)

The application can then use display_scale to render the value correctly.

For example:

				
					value = 1.5
display_scale = 2

rendered value = 1.50

This pattern is useful when the entered scale is metadata, not just formatting.

Workaround 5: Normalize Migration Tests

If you are comparing PostgreSQL and YugabyteDB output during a migration, do not rely on raw text output for unconstrained NUMERIC columns.

This may fail even when the values are numerically equivalent.

Instead, normalize the comparison.

For example, compare numeric equality:

				
					SELECT
    id,
    v = expected_v AS values_match
FROM validation_table;

Or compare using a declared format:

				
					SELECT
    id,
    to_char(v, 'FM999999990.0000') AS formatted_v
FROM t
ORDER BY id;

Or compare after explicitly rounding to the required scale:

				
					SELECT
    id,
    round(v, 4) AS normalized_v
FROM t
ORDER BY id;

The right choice depends on whether your migration test is validating numeric correctness, display format, or user-entered precision.

Migration tip: For unconstrained NUMERIC columns, separate numeric-value validation from display-scale validation. They are not the same thing.

Quick Decision Table

Requirement	Recommended Approach
Numeric correctness only	Unconstrained `NUMERIC` is usually fine
Fixed decimal places are required	Use `NUMERIC(precision, scale)`
Only display formatting matters	Use `to_char()` or application formatting
Original user-entered scale must be preserved	Store the display scale or original text separately
PostgreSQL migration test compares text output	Normalize output or compare numeric values instead of raw text

Final Takeaway

YugabyteDB currently has a PostgreSQL compatibility issue around trailing zeros in unconstrained NUMERIC values.

The numeric value is still correct… Normal calculations should not be affected.

But the original trailing-zero scale may not round-trip the same way it does in PostgreSQL.

That means this can affect:

● scale() checks
● text-based migration validation
● display formatting
● application logic that treats entered scale as meaningful metadata

Until the issue is resolved, the safest workaround is to be explicit.

Bottom line: If scale matters, declare it with NUMERIC(precision, scale). If scale is only for display, format it with to_char(). If the original user-entered scale is business metadata, store that scale separately.

Have Fun!