Postgres row_to_json precision for numeric type

Question

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values.

When row_to_json returns the json, values like 9.98 becomes 9.98000000000000043 or 6.30 becomes 6.29999999999999982 in these numeric columns.

Is there a way to impose precision at the json creation function? There are no other data issues in the table which are affecting other modules. Changing the column type to numeric(12,2) etc. is not possible since the table has over 2 billion rows(the table would be locked for 30+hours) and would affect other production modules.

--update--

I should have made the question a little bit clearer with the use case.

row_to_json is used in a trigger to audit updates in the DB. The json result is stored in another table and this same audit function is used for multiple tables(100+) where there may be other tables with this same issue.

One solution would be to handle it at the application level when retrieving the jsons from the audit table, but I would like to see if there is a function that can handle it in the DB itself.

Answer 1

You can round the values:

select row_to_json(t)
from (
  select id, 
         round(value_1, 2) as value_1, 
         round(value_2, 2) as value_2
  from test
) t;

If you need that more often and don't want to type it all the time, create a view that returns the rounded values.

Answer 2

Using a normal "select row_to_json(table) from table" gives the right value without any loss of precision. But in the trigger(a BEFORE UPDATE trigger), using "row_to_json(NEW)" leads to a loss in precision.

I tried the following using PostgreSQL 14 and psql shell. I found that the function row_to_json(NEW) creates JSON data correctly. The numeric column from the table has the exact value in the JSON.

# Create a table with rows, and a column `data` with `numeric` type
CREATE TABLE rowj (
    id serial NOT NULL PRIMARY KEY,
    name text NOT NULL,
    data numeric NOT NULL
);

# Sample data
INSERT INTO rowj VALUES (DEFAULT, 'one', 12);
INSERT INTO rowj VALUES (DEFAULT, 'two', 6.3);
INSERT INTO rowj VALUES (DEFAULT, 'three', 7.98);

# Query table
table rowj;
 id |  name   |  data
----+---------+--------
  1 | one     |     12
  2 | two     |    6.3
  3 | three   |   7.98

# Audit table for `rowj` with a `jsonb` type column
CREATE TABLE rowj_audit (
    id serial NOT NULL PRIMARY KEY,
    details JSONB
)

# Create a function for the trigger
# Note the usage of `row_to_json(NEW)` 
CREATE FUNCTION rowj_fn() RETURNS trigger AS $rowj_fn$
    BEGIN
        INSERT INTO rowj_audit VALUES (DEFAULT, row_to_json(NEW));
        RETURN NEW;
    END;
$rowj_fn$ LANGUAGE plpgsql;

# Create before update trigger on `rowj`, uses the above function
CREATE TRIGGER rowj_fn BEFORE UPDATE ON rowj
    FOR EACH ROW EXECUTE FUNCTION rowj_fn();

# Update `rowj` table rows
UPDATE rowj SET name='one-1' WHERE id=1;
UPDATE rowj SET name='two-2', data=123.456 WHERE id=2;
UPDATE rowj SET name='three-3' WHERE id=3;

# Query both tables, and review the data
table rowj;
 id |  name     |    data
----+-----------+-----------
  1 | one-1     |       12
  2 | two-2     |  123.456
  3 | three-3   |     7.98

table rowj_audit;
id |                    details
----+-----------------------------------------------
  1 | {"id": 1, "data": 12, "name": "one-1"}
  2 | {"id": 2, "data": 123.456, "name": "two-2"}
  3 | {"id": 3, "data": 7.98, "name": "three-3"}