CODEWITHSUNDEEP
postgresql
code writer 3000
code writer 3000

Reputation: 354

Postgres generated columns returning 0 for each record

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:

Query result

Upvotes: 0

Views: 118

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19664

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

Upvotes: 1

Related Questions