Question 89395 in PostgreSQL Question posted in
The official documentation can be found here.

Updating a products inventory label based on the inventory of each sku for each product – Postgresql

I have the inventory label for each product and its skuid. A product can have 1 more Skuid.

product_skus_inventories table:

ProductID   SkuID   Inventory_Label
123         a1      InStock
123         a2      OutOfStock
123         a3      NULL

I want to update the below table that is a summary:

product_summary
- product_id
- inventory_label (values can be InSock, OutOfStock or Partial)

The logic is as follows:

  1. if all SkuID’s for a given productID "InStock" or NULL, then "InStock".
  2. if any of the SkuIds have "InStock", and other’s have different value then "Partial".
  3. otherwise, "OutOfStock".

Is it possible to have this type of logic in a update query?

Tags:

5

Answers


  1. 0

    You can create a view of your table that generates the status which can then be easily queried when you need the values. If instead you need to run an UPDATE to materialize the inventory label, then you can use the SELECT inside JOIN LATERAL to get the status to insert for a given product_id.

    The logic for the status is as follows:

    1. Check if the number of rows for a given product_id matches the number of rows after filtering out out_of_stock.
    2. Since we know that at least 1 out_of_stock exists for this product_id as the previous clause didn’t match, we just need to check if there exists at least 1 in_stock or NULL.
    3. Else, out_of_stock.
    CREATE VIEW product_summary as (
    SELECT DISTINCT product_id, _status.inventory_label 
    FROM product_skus_inventories psi_outer
    JOIN LATERAL (
        SELECT CASE WHEN (COUNT(*) = (COUNT(*) FILTER (WHERE psi_inner.inventory_label IS NULL OR psi_inner.inventory_label='in_stock'))) THEN 'in_stock' 
                    WHEN (EXISTS (SELECT 1 FROM product_skus_inventories _psi WHERE psi_outer.product_id = _psi.product_id AND (_psi.inventory_label IS NULL OR _psi.inventory_label='in_stock'))) THEN 'partial'
                    ELSE 'out_of_stock'
                    END AS inventory_label
        FROM product_skus_inventories psi_inner
        WHERE psi_inner.product_id = psi_outer.product_id
    ) AS _status ON true
);
    Login or Signup to reply.
  2. 0

    Collect label combinations per product and specify summary of each combination case: demo

    select productid,
       case array_agg(distinct inventory_label)
              filter(where inventory_label is not null)
         when '{InStock}'            then 'InStock'
         when '{InStock,OutOfStock}' then 'Partial'
         else 'OutOfStock'
       end as inventory_label
from product_skus_inventories
group by productid;

    This can be used as an update source, as shown at the end of the demo.

    Making this a view or a materialized view, using it as a source for an insert into...on conflict do update or a merge(also in the demo), could potentially make more sense.

    Login or Signup to reply.
  3. 0

    Basic query

    With original table definition

    Don’t count or aggregate all rows. You are looking for a boolean result, basically. And these two aggregate functions happen to fit your task description exactly:

    Makes for a fast and simple query:

    SELECT productid
     , CASE WHEN bool_and(inventory_label = 'InStock') THEN 'InStock'
            WHEN bool_or (inventory_label = 'InStock') THEN 'Partial'
            ELSE                                            'OutOfStock' END AS label
FROM   product_skus_inventories
GROUP  BY 1
ORDER  BY 1;  -- optional

    fiddle

    Better with boolean

    product_skus_inventories.inventory_label can be "in", "out", or null. That’s a boolean quantity. Use the fitting, superior data type. Only occupies 1 byte and makes everything shorter, simpler and faster.

    To convert the table (triggers a complete table rewrite with exclusive lock):

    ALTER TABLE product_skus_inventories
ALTER COLUMN inventory_label TYPE boolean USING inventory_label = 'InStock'; -- that's all!

    Then run VACUUM ANALYZE product_skus_inventories once.

    Now, the query is even simpler and faster:

    SELECT productid
     , CASE WHEN bool_and(inventory_label) THEN 'InStock'
            WHEN bool_or(inventory_label)  THEN 'Partial'
            ELSE                                'OutOfStock' END AS label
FROM   product_skus_inventories
GROUP  BY 1
ORDER  BY 1;  -- optional

    fiddle

    "Update" summary table

    The appropriate action is an UPSERT, really. INSERT ... ON CONFLICT ... DO UPDATE ... in Postgres.

    Assuming this table definition:

    CREATE TABLE product_summary (
  product_id      int PRIMARY KEY  -- needs PK or UNIQUE contraint / index
, inventory_label text             -- still text?
);

    INSERT INTO product_summary AS p (product_id, inventory_label)
SELECT productid
     , CASE WHEN bool_and(inventory_label) THEN 'InStock'
            WHEN bool_or(inventory_label)  THEN 'Partial'
            ELSE                                'OutOfStock' END AS label
FROM   product_skus_inventories
GROUP  BY 1
ORDER  BY 1  -- optional
ON     CONFLICT (product_id) DO UPDATE
SET    inventory_label = EXCLUDED.inventory_label
WHERE  p.inventory_label IS DISTINCT FROM EXCLUDED.inventory_label;

    fiddle

    Note the added WHERE clause. This skips all updates that wouldn’t change the state, typically making the query a lot cheaper. See:

    Login or Signup to reply.
  4. 0

    Alternatives to my initial answer, at @Mike Organek‘s suggestion to try other aggregate functions:

    • count(distinct inventory_label) combinations: demo

      select productid,
       case count(distinct inventory_label) --this auto-skips `null`
           when 2 then 'Partial'            --2 means {InStock,OutOfStock}
           when 1 then min(inventory_label) --we want whatever that 1 was
           else        'OutOfStock'--unless null-only labels mean 'InStock'
       end
from product_skus_inventories 
group by productid;

    • With bits, using encodings: demo

      • 2 (binary 10) for InStock,
      • 1 (binary 01) for OutOfStock,
      • getting a 11(decimal 3) from bit_or() when both are found, or 10(2) for just InStock
      select productid,
       case bit_or((inventory_label='InStock')::int+01)  --F=01, T=10
         when 3 then 'Partial'--3 is binary 11,found 10 as well as 01
         when 2 then 'InStock'--2 is InStock, because bit_or found 10's only
         else        'OutOfStock'--unless null-only labels mean 'InStock'
       end as inventory_label
from product_skus_inventories
group by productid;

    I prefer the array_agg(distinct inventory_label) for its readability. These are all simple, but that one also self-comments. Still, count() is a filter shorter, and bit_or() is faster.

    Login or Signup to reply.
  5. 0

    How about using this kind of approach.

    UPDATE product_summary
SET inventory_label =
  CASE 
    WHEN EXISTS (
      SELECT 1
      FROM product_skus_inventories
      WHERE ProductID = product_summary.product_id
      AND Inventory_Label IN ('InStock', NULL)
    ) AND NOT EXISTS (
      SELECT 1
      FROM product_skus_inventories
      WHERE ProductID = product_summary.product_id
      AND Inventory_Label NOT IN ('InStock', NULL)
    ) THEN 'InStock'
    WHEN EXISTS (
      SELECT 1
      FROM product_skus_inventories
      WHERE ProductID = product_summary.product_id
      AND Inventory_Label = 'InStock'
    ) THEN 'Partial'
    ELSE 'OutOfStock'
  END;

    This query first checks if all SkuIDs for a given productID are either "InStock" or NULL, and updates the inventory_label to "InStock" if that’s the case.

    If not, it checks if any SkuIDs have "InStock" and updates the inventory_label to "Partial" if that’s the case.

    If neither condition is met, it updates the inventory_label to "OutOfStock".

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search