Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

CREATE 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)

items_stock:
  (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}
share|improve this question

1 Answer 1

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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