McBoman McBoman - 7 months ago 15
SQL Question

Postgres automatically rounds the number on insert

I have a table that looks like the following:

CREATE TABLE word(
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
word TEXT NOT NULL,
count INT NOT NULL,
ratio NUMERIC(10, 3) NOT NULL,
percent_of_total NUMERIC(10, 3) NOT NULL,
daily_count_id UUID REFERENCES daily_count(id)
);


I then tries to insert with this statement:

INSERT INTO word (word, count, ratio, percent_of_total, daily_count_id)
VALUES ('test', 5, 5/214, 5*100/214,
(SELECT id from daily_count WHERE day_of_count = CURRENT_DATE+1));


It works. It inserts the value when selecting it from the table then the numeric values has been rounded like the following:

67035a35-e5df-495b-95d5-cb3b4041c7b4 test 5 0.000 2.000 91858e7a-3440-4959-9074-9d197d6c97fc


The values 2.000 and 0.000 are rounded but I need them to be the precise value.

I'm using the DataGrip IDE but I do not think it has anything to do with it.

Answer

5/214 will be executed as integer division. I.e. the result will be integer.

If you want floating point division, you can simply do 5.0/214.

(Or use cast, e.g cast(5 as NUMERIC(10, 3)) / 214.)