jsonpostgresqlroundingplpgsqltruncated

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


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


Solution

  • 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.