PostgreSQL update last data inserted field

Question

I try to handle this situation in a PostgreSQL database. I insert data to a table called "Invoice" by programming:

invoice_id    account_id    amount  current 
    1            a2322        3      null
    2            a2322       10      null
    3            b4354        1      null
    4            c8099        2      null
    5            a2322        4      null
    6            b4354       -5      null

so, when a new record is inserted, I want to update the current field with the sum of amounts like this:

invoice_id    account_id    amount  current 
    1            a2322        3      null
    2            a2322       10      null
    3            b4354        1      null
    4            c8099        2      null
    5            a2322        4      null
    6            b4354       -5      null
    7            a2322        4      result(sum of amount: 21)
    8            b4354        3      result(sum of amount: -1)

Can I do that inside PostgreSQL without programming?

S-Man · Accepted Answer · 2018-10-30 07:57:12Z

2

You can do this in a trigger AFTER INSERT OR UPDATE OR DELETE that runs FOR EACH STATEMENT. This is programming too, albeit not on the client side.

In the trigger you can calculate the sums like this (assuming that invoice_id is serial):

INSERT INTO invoice
   (account_id, amount, current)
SELECT account_id, sum(amount), 'result'
FROM invoice
WHERE current IS NULL
GROUP BY account_id
HAVING count(*) > 1
ON CONFLICT (account_id, current)
DO UPDATE
   SET amount = EXCLUDED.amount;

This requires a UNIQUE constraint on (account_id, current) and makes use of the fact that unique constraints allow “duplicate” as long as NULLs are incolved.

edited Oct 30, 2018 at 7:57

S-Man

24k9 gold badges51 silver badges78 bronze badges

answered Oct 30, 2018 at 6:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

jlee88my · Accepted Answer · 2018-10-30 12:51:16Z

0

This is not technically a direct answer to your question. But what you want is a running number. You may not need to store that info. It can be calculated on the fly:

SELECT *, SUM(amount) OVER(ORDER BY invoice_id) AS running_total;

This way, you won't need to worry about reconciliation and/or update errors. Especially if you later need to update the quantity, the running total will be all messed up, with SUM() OVER(), it will always be consistent.

edited Oct 30, 2018 at 12:51

answered Oct 30, 2018 at 11:45

jlee88my

3,0531 gold badge23 silver badges28 bronze badges

Collectives™ on Stack Overflow

PostgreSQL update last data inserted field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related