Postgres automatically rounds the number on insert

Question

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.

This works thanks. If you create an answer then I will close this one. — user3316920
– user3316920, Commented May 4, 2016 at 9:51

jarlh · Accepted Answer · 2016-05-04 09:55:04Z

2

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

answered May 4, 2016 at 9:55

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres automatically rounds the number on insert

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related