CODEWITHSUNDEEP
postgresql
john johnson
john johnson

Reputation: 730

postgresql store decimal value with trailing zero using type real

i'm new to sql and trying to store a decimal value so that it gets stored truly as "6.0". for example, 

INSERT INTO public.mytable(the_num) VALUES (6.0)

but the result is storing as "6" instead of "6.0" , of course any non-zero number like 6.1 or 6.5 is stored fine.

how can I store the value without having the trailing zero truncated ? is there some other data type i should be using?

Upvotes: 2

Views: 3530

Answers (1)

klin
klin

Reputation: 121474

Real values are stored in binary format, so you can only decide in what form they will be shown. You can cast real values to numeric using (precision, scale) like this:

with my_table(the_num) as (
values
    (6.0::real),
    (6.1),
    (6.15)
)

select the_num, the_num::numeric(15,1), the_num::numeric(15,2)
from my_table

 the_num | the_num | the_num 
---------+---------+---------
       6 |     6.0 |    6.00
     6.1 |     6.1 |    6.10
    6.15 |     6.2 |    6.15
(3 rows)

Alternatively, you can use the function to_char(), e.g.

select the_num, to_char(the_num, '999D9'), to_char(the_num, '999D99')
from my_table

 the_num | to_char | to_char 
---------+---------+---------
       6 |    6.0  |    6.00
     6.1 |    6.1  |    6.10
    6.15 |    6.2  |    6.15
(3 rows)

You can also use the numeric type instead of real in the table definition, e.g.:

create table my_table (
    the_num numeric(15, 2)
);

In this case the values will be stored with the defined scale.

Read more about Numeric Types in the documentation.

Upvotes: 4

Related Questions