How to truncate double precision value in PostgreSQL by keeping exactly first two decimals?

Question

I'm trying to truncate double precision value when I'm build json using json_build_object() function in PostgreSQL 11.8 but with no luck. To be more precise I'm trying to truncate 19.9899999999999984 number to ONLY two decimals but making sure it DOES NOT round it to 20.00 (which is what it does), but to keep it at 19.98.

BTW, what I've tried so far was to use:
1) TRUNC(found_book.price::numeric, 2) and I get value 20.00
2) ROUND(found_book.price::numeric, 2) and I get value 19.99 -> so far this is closesest value but not what I need
3) ROUND(found_book.price::double precision, 2) and I get

[42883] ERROR: function round(double precision, integer) does not exist

Also here is whole code I'm using:

create or replace function public.get_book_by_book_id8(b_id bigint) returns json as
$BODY$
declare
    found_book book;
    book_authors json;
    book_categories json;
    book_price double precision;
begin
    -- Load book data:
    select * into found_book
    from book b2
    where b2.book_id  = b_id;

    -- Get assigned authors
    select case when count(x) = 0 then '[]' else json_agg(x) end into book_authors
    from (select aut.*
        from book b
        inner join author_book as ab on b.book_id = ab.book_id
        inner join author as aut on ab.author_id = aut.author_id
        where b.book_id = b_id) x;

    -- Get assigned categories
    select case when count(y) = 0 then '[]' else json_agg(y) end into book_categories
    from (select cat.*
        from book b
        inner join category_book as cb on b.book_id = cb.book_id
        inner join category as cat on cb.category_id = cat.category_id
        where b.book_id = b_id) y;

    book_price = trunc(found_book.price, 2);
    -- Build the JSON response:
    return (select json_build_object(
        'book_id', found_book.book_id,
        'title', found_book.title,
        'price', book_price,
        'amount', found_book.amount,
        'is_deleted', found_book.is_deleted,
        'authors', book_authors,
        'categories', book_categories
    ));
end
$BODY$
language 'plpgsql';

select get_book_by_book_id8(186);

How do I achieve to keep EXACTLY ONLY two FIRST decimal digits 19.98 (any suggestion/help is greatly appreciated)?

P.S. PostgreSQL version is 11.8

What is your exact PostgreSQL version ? I cannot reproduce in 12.3. What is the data type of `found_book.price` ? — pifor, Jun 16 '20 at 11:12
You'll have to add a complete SQL statement that displays the undesirable rounding. I am not sure what exactly you are doing. — Laurenz Albe, Jun 16 '20 at 11:24
What's the original data type of `found_book.price`? That you pass the result into `json_build_object` should not matter. — Bergi, Jun 16 '20 at 11:26
@LaurenzAlbe: I've added code you have asked for and added additonal tag for plgpgsql which I've missed (sorry for that) — NikolaS, Jun 16 '20 at 11:29
BTW, I've also added (i.e. declared) NEW variable `book_price` to try to achieve what @pifor suggested in his answer, but no luck — NikolaS, Jun 16 '20 at 11:31

pifor · Accepted Answer · 2020-06-16T11:37:06.373

3

In PostgreSQL 11.8 or 12.3 I cannot reproduce:

# select trunc('19.9899999999999984'::numeric, 2);
 trunc 
-------
 19.98
(1 row)

# select trunc(19.9899999999999984::numeric, 2);
 trunc 
-------
 19.98
(1 row)

# select trunc(19.9899999999999984, 2);
 trunc 
-------
 19.98
(1 row)

Actually I can reproduce with the right type and a special setting:

# set extra_float_digits=0;
SET
# select trunc(19.9899999999999984::double precision::text::numeric, 2);
 trunc 
-------
 19.99
(1 row)

And a possible solution:

# show extra_float_digits;
 extra_float_digits 
--------------------
 3
(1 row)

select  trunc(19.9899999999999984::double precision::text::numeric, 2);
 trunc 
-------
 19.98
(1 row)

But note that:

Note: The extra_float_digits setting controls the number of extra significant digits included when a floating point value is converted to text for output. With the default value of 0, the output is the same on every platform supported by PostgreSQL. Increasing it will produce output that more accurately represents the stored value, but may be unportable.

edited Jun 16 '20 at 11:37

answered Jun 16 '20 at 11:13

pifor

7,419
2
8
16

I've updated my question with code of `json_build_object()` function that I'm using so it's clear I can not pass numeric value as a string (like in your first and second example). – NikolaS Jun 16 '20 at 11:19
Strange, I cannot reproduce that on Fedora 30 with PostgreSQL 12.3. – Laurenz Albe Jun 16 '20 at 11:40
I agree: I cannot reproduce with PG 12.3. But it works with PG 11.8 on Centos 7.8.2003. – pifor Jun 16 '20 at 11:42
@pifor : can this 3 time casting (double precision>text>numeric) impose any kind of performance issues...like slowing down execution time, that is make it last longer in very obvious way for let's say 2000 records (**book** instances)? – NikolaS Jun 16 '20 at 11:54
1

@NikolaS: a quick test does not show overhead using psql `\timing` when running the 3 conversions with `SELECT` versus just displaying the number with `SELECT` 19...` without any conversion. There should be no performance issue. – pifor Jun 16 '20 at 12:03
@NikolaS: however you should take into consideration that this solution does not work with the PostgreSQL version 12. – pifor Jun 16 '20 at 12:04
@pifor: so in case of migrating to new version (let's say 12.3 version) my code will break and I need to find some other workaround ...or just manually change code when switching to new PostgreSQL version? – NikolaS Jun 16 '20 at 12:06
1

@NikolaS: yes you need to find a workaround. If possible try to switch to `numeric` because as doc. says: *if you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.* – pifor Jun 16 '20 at 12:10

score 1 · Answer 2 · answered Jun 16 '20 at 11:37

As @pifor suggested I've managed to get it done by directly passing trunc(found_book.price::double precision::text::numeric, 2) as value in json_build_object like this:

json_build_object(
        'book_id', found_book.book_id,
        'title', found_book.title,
        'price', trunc(found_book.price::double precision::text::numeric, 2),
        'amount', found_book.amount,
        'is_deleted', found_book.is_deleted,
        'authors', book_authors,
        'categories', book_categories
    )

Using book_price = trunc(found_book.price::double precision::text::numeric, 2); and passing it as value for 'price' key didn't work.

Thank you for your help. :)

How to truncate double precision value in PostgreSQL by keeping exactly first two decimals?

2 Answers2