calculate column in PostgreSQL

Question

To the alter column I want update values calculates from two another columns. I write:

UPDATE daily_eu SET total_cases_per_million = (total_cases/population)*1000000;

Result is for all records value:

0.000

How to fix to give correct result of my calculation?

Show your table definition (CREATE TABLE statement). You probably fell victim to integer division. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 19, 2022 at 22:25

Erwin Brandstetter · Accepted Answer · 2022-06-19 22:34:51Z

1

To avoid integer division (which truncates fractional digits):

UPDATE daily_eu
SET    total_cases_per_million = total_cases * 1000000.0 / population;

The numeric constant with a fractional digit (1000000.0) resolves to type numeric instead of integer, avoiding integer division. It's also typically more precise to multiply first.

answered Jun 19, 2022 at 22:27

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

calculate column in PostgreSQL

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related