Increment a column value of an entity in an SQL table

I’ve set up the following table:

CREATE TABLE transactions (txn_id SERIAL, stock VARCHAR NOT NULL, qty INT NOT NULL, user_id INT NOT NULL);

On inserting few rows, the table looks like this:

txn_id | stock  | qty | user_id
--------+--------+-----+---------
      1 | APPL   |   2 |       1
      2 | GOOGLE |   3 |       4
      3 | TSLA   |   1 |       2

Now, while adding a new insert into the table, for example – INSERT INTO transactions (stock, qty, user_id) VALUES ('APPL', 3, 1) if ‘stock‘ and ‘user_id‘ match (as in the above example), i only want the quantity to be updated in the database. Such as in this case, the row 1 entity should have its ‘qty’ column value incremented by +3.

Is there a solution to this without using any ORM like SQLalchemy etc. ?

Answer

You want on conflict. First set up a unique constraint on the stock/user_id columns:

alter table transactions add constraint unq_transactions_stock_user_id unique (stock, user_id);

Then use this with an on conflict statement:

insert into transactions (stock, qty, user_id) 
    values ('APPL', 3, 1)
    on conflict on constraint unq_transactions_stock_user_id do update
        set qty = coalesce(transactions.qty, 0) + excluded.qty;