postgres match - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-merge.html

postgres=# \d+ product_stock
                                     Table "public.product_stock"
    Column    |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------------+---------+-----------+----------+---------+---------+--------------+--------------
 prod_id      | integer |           |          |         |         |              |
 qty_in_stock | integer |           |          |         |         |              |

postgres=# \d+ stock_adjustment
                                Table "public.stock_adjustment"
   Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
------------+---------+-----------+----------+---------+---------+--------------+--------------
 prod_id    | integer |           |          |         |         |              |
 adjust_qty | integer |           |          |         |         |              |

postgres=# MERGE INTO product_stock A
postgres-# USING stock_adjustment B
postgres-# ON B.prod_id = A.prod_id
postgres-# WHEN MATCHED AND qty_in_stock + adjust_qty > 0 THEN  A
postgres-#   UPDATE SET qty_in_stock = qty_in_stock + adjust_qty
postgres-# WHEN MATCHED THEN                                    B
postgres-#   DELETE
postgres-# WHEN NOT MATCHED AND adjust_qty > 0 THEN             C
postgres-#   INSERT VALUES (B.prod_id, B.adjust_qty)
postgres-# WHEN NOT MATCHED THEN                                D
postgres-#   DO NOTHING;

Alternative solution:

 WITH results as (
      SELECT ...
    ),

    inserted_rows AS (
      INSERT ...
      SELECT * FROM results
      ...
    ),

    deleted_rows AS (
      DELETE ...
      USING results
      ... 
    )

    UPDATE ...
    FROM results