Postgres generated columns returning 0 for each record

Question

I'm trying to create a generated column in postgre but it's not generating right. I currently have a column for each state's number of electors and cost (for an election game) and I want to create a column that generates that state's value which divides the two then multiplies it by 1000. Here is my table definition:

CREATE TABLE public.states (
    id serial4 NOT NULL,
    "name" varchar NOT NULL,
    "cost" int8 NULL,
    electors int2 NULL
);

Here is the query that I made to insert the generated column:

ALTER TABLE public.states
ADD value float(8) generated always as (electors * 1000 / cost) STORED;

This is my result. Every column is 0 but I should be getting float values that are less than 1 and greater than 0:

It looks like the math is being done on ints, so 0.3 = 0 and then the result is being stored as a float. — Ross Bush
– Ross Bush, Commented Jun 27, 2022 at 22:48

Adrian Klaver · Accepted Answer · 2022-06-27 23:20:14Z

1

Integer division:

select (3 *1000)/10000; 
0

Do something like:

select round((3 * 1000)/10000::numeric, 2);  
0.30

Or translated:

UPDATE

(round((electors * 1000) / cost::numeric, 2))

edited Jun 27, 2022 at 23:20

answered Jun 27, 2022 at 22:49

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

4 Comments

code writer 3000 Over a year ago

(round(electors * 1000 / cost::numeric, 2)) works, but some values don't get rounded and I'll get something like 0.30000001.

Adrian Klaver Over a year ago

See UPDATE. Try select round((electors * 1000) / cost::numeric, 2) from public.states to see what is returned?

code writer 3000 Over a year ago

I don't see a difference @Adrian Klaver

Adrian Klaver Over a year ago

In your question provide example of what works and what does not.

Collectives™ on Stack Overflow

Postgres generated columns returning 0 for each record

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related