SQL Upsert with ON CONFLICT and RETURNING

-- Schema assumptions (PostgreSQL 15+):
--   inventory(sku text PRIMARY KEY, quantity integer NOT NULL CHECK (quantity >= 0),
--             price_cents integer NOT NULL CHECK (price_cents >= 0),
--             version integer NOT NULL DEFAULT 1,
--             updated_at timestamptz NOT NULL DEFAULT now())
-- The TEMP table below mirrors that shape; no external service is required.

CREATE TEMP TABLE inventory (
    sku         text PRIMARY KEY,
    quantity    integer NOT NULL CHECK (quantity >= 0),
    price_cents integer NOT NULL CHECK (price_cents >= 0),
    version     integer NOT NULL DEFAULT 1,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO inventory (sku, quantity, price_cents, version) VALUES
    ('KB-01', 10, 4900, 1),
    ('MS-02', 4, 2500, 1);

-- One statement handles both paths: `excluded` is the proposed row.
-- Existing SKUs add incoming stock; new SKUs are inserted untouched.
INSERT INTO inventory AS inv (sku, quantity, price_cents, version)
VALUES
    ('KB-01', 12, 5200, 1),
    ('MS-02', 1, 2400, 1),
    ('HD-03', 7, 8900, 1)
ON CONFLICT (sku) DO UPDATE
SET quantity    = inv.quantity + EXCLUDED.quantity,
    price_cents = EXCLUDED.price_cents,
    version     = inv.version + 1,
    updated_at  = now()
WHERE EXCLUDED.version = inv.version
RETURNING sku, quantity, price_cents, version;

-- A stale writer carries version 0. It matches no current row, so the
-- update is skipped and RETURNING yields zero rows instead of overwriting.
INSERT INTO inventory AS inv (sku, quantity, price_cents, version)
VALUES ('KB-01', 5, 1, 0)
ON CONFLICT (sku) DO UPDATE
SET quantity    = EXCLUDED.quantity,
    price_cents = EXCLUDED.price_cents,
    updated_at  = now()
WHERE EXCLUDED.version = inv.version
RETURNING sku, quantity, price_cents, version;