How to Add Specific Column When inserting Value on Postgres

Question

I am trying to insert a value and calculating the value that i inserted into one column.

the example, basically I have one table with 3 column on it, id, amount, total:

id | amount | total
1 | 100 | 100 
2 | 200 | 300
3 | -100 | 200

expected result:

every time a new amount value is entered, I want the value in column total to be added with that value

INSERT INTO public.tb_total_amount
(id, amount, total, create_time)
VALUES(1, 100, balance+amount, NOW());

is it ok to accumulate the negative value? and, anyone can correct my query? thank you

Tim Biegeleisen · Accepted Answer · 2019-02-03 11:33:59Z

1

I recommend against doing this, and I instead suggest just using SUM as an analytic function:

SELECT
    id,
    amount,
    SUM(amount) OVER (ORDER BY id) total
FROM yourTable;

The logic behind this answer is that your rolling sum total is derived, and not original, data. Therefore, it is better to just compute it on the fly when you need it, rather than storing it.

If you really want to insert the correct total during a single insert, you may try:

INSERT INTO public.tb_total_amount (amount, total, create_time)
SELECT
    100,
    (SELECT COALESCE(amount, 0) FROM public.tb_total_amount
     ORDER BY id DESC LIMIT 1) + 100,
    NOW();

edited Feb 3, 2019 at 11:33

answered Feb 3, 2019 at 8:04

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

6 Comments

Code On Over a year ago

can you explain why against?

Tim Biegeleisen Over a year ago

The rolling sum is derived data, and in general it should probably not be stored in your table.

Code On Over a year ago

what if i still want to store the value into table? do you have a solution for it?

Tim Biegeleisen Over a year ago

@CodeOn I gave you an option using INSERT INTO ... SELECT.

Code On Over a year ago

the insert query with select will got error when there's no row before

|

Collectives™ on Stack Overflow

How to Add Specific Column When inserting Value on Postgres

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related