Is there a way to set value in specific position inside array based on information from other columns (PostgreSQL-9.3 and later)?

For example I would like to select an item and its stock information from following tables:

Table items:

  id integer NOT NULL

Table item_stock (containing shop-specific information like stock and prices)

CREATE TABLE item_stock (
    item_id integer NOT NULL,
    shop_id integer NOT NULL,
    stock integer,
    cost numeric(19,3),

Now with having following data inside my tables (SQLFiddle):

items containing 4 products:
 (1), (2), (3), (4)

  (1, 1, 2, 10),
  (1, 2, 0, 9),
  (2, 2, 0, 9),
  (3, 1, 3, 22);

What I would like to end up with is query producing following results, where stock column contains stock info from specific shops like this (shop_id=1, shop_id=2):

id, stock
1, {2, 0}
2, {0, 0}
3, {3, 0}
4, {0, 0}

This is the query I was able to come up (with some brute-force):

SELECT b.id, array_agg(b.stock) FROM (
  SELECT a.*, COALESCE(i_s.stock, 0) as stock FROM (
    SELECT id, generate_series(1, 2) as n FROM items  
  ) as a
  LEFT OUTER JOIN item_stock i_s ON a.id = i_s.item_id AND a.n = i_s.shop_id
) as b GROUP by b.id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.