Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE stocks
(
    id text, /* id of the stock */
    quantity int, /* original quantity when the stock is first received */
    location_id int, /* where stock is physically located at */
    attributes jsonb /* properties of the stock like size, color, length, etc. */
);

CREATE TABLE transfers
(
    id text, /* transfer id */
    stock_id text, /* id of the stock to be transferred*/
    transfer_quantity int, /* how much of the original stock quantity should be transferred */
    to_location_id int /* can be null, if null = used or sold, if not null = transferred to another location */
);

/*stocks*/
INSERT INTO stocks(id, quantity, location_id) VALUES ('id1', 10, 1);
INSERT INTO stocks(id, quantity, location_id) VALUES ('id2', 10, 2);

/*transfers, (used or sold quantities from specific stocks) */
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id1', 1);
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id1', 1);

INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id2', 1);
INSERT INTO transfers(stock_id, transfer_quantity) VALUES ('id2', 2);

/*transfer some quantities of a certain stock to another location inside the warehouse */
INSERT INTO transfers(stock_id, transfer_quantity, to_location_id) VALUES ('id2', 1, 1);

CREATE TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

id	location_id	quantity	transferred_quantity_total	remaining_qty
id1	1	10	2	8
id2	2	10	3	7

SELECT 2