Postgres: Generated column with parenthesis operation

Question

I wanna add a generated column in my PostgreSQL table. The problem is when I use operation with parenthesis, I got zero results.

ALTER TABLE my_table 
add COLUMN my_column decimal(4,3) GENERATED ALWAYS AS ((price-buy_price)/price) STORED;

All the result in my_column is 0.000. Then I try to avoid the parenthesis in my operation so my query looks like:

ALTER TABLE my_table 
add COLUMN my_column2 decimal(4,3) GENERATED ALWAYS AS (price-buy_price/price) STORED;

That's work but the result is not what I want. I wonder why it fails every time I use parenthesis and it works when I do operations like (a+b) or (a+b/c) or (a*b-c/d) etc.

Is anyone has the solution? Thank you.

@DaleK Oh? Sorry I am new at DB management and don't know the difference between that. Ok, I will edit my question, thank you for the suggestion. — Hermawan Wiwid
– Hermawan Wiwid, Commented May 20, 2020 at 4:38
@Nick Ah, my column type of price and buy_price are an integer. I think that's why I got zero results. Let's check <dbfiddle.uk/…> — Hermawan Wiwid
– Hermawan Wiwid, Commented May 20, 2020 at 4:52
Yup, that's the problem. Changing the formula to (1.0 * (price-buy_price)/price) solves it: dbfiddle.uk/… — Nick
– Nick, Commented May 20, 2020 at 4:53

user330315 · Accepted Answer · 2020-05-20 04:59:24Z

1

If your columns are integer columns the division is also an integer division leaving you with no decimal digits. You need to cast at least one of the expressions in the division:

ALTER TABLE my_table 
   add my_column decimal(4,3)  
      GENERATED ALWAYS AS ( (price-buy_price) / price::numeric) STORED;

answered May 20, 2020 at 4:59

user330315

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

2 Comments

Hermawan Wiwid Over a year ago

Cool, that's also work. But what the difference if multiplied by 1.0? like (1.0 * (price-buy_price)/price)

user330315 Over a year ago

@HermawanWiwid: no real difference, just different coding style. The 1.0 * also forces a non-integer expression (because 1.0 is a float constant)

Collectives™ on Stack Overflow

Postgres: Generated column with parenthesis operation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related