How to Populate Arrays from Other Tables in PostgreSQL 9.4

arraypostgresqlpostgresql-9.4

Things go inside other things. More than one can go inside and each one can be inside many different containers. This is for a on-line shopping site where assorted things that we get from our suppliers are bundled together, given a web-page and then sold as that collective. It all used to be tracked with a bunch of horrible CSV files but I'm setting up a proper database now.

create table collections(
    sku character varying(50),
    components text[]);

create table products(
    ordering_id character varying(50),
    inside text[] );

inside's elements are skus from collections and components elements are ordering_ids from products.

The contents of these easily fill in both columns in the products table and sku in collections. Now I want to fill in components: preferably with some tricky SQL statement. The best I could come up with is:

with new_values as ( select ordering_id, inside from products )
    update collections set components = components ||   
        nv.ordering_id::text  from new_values nv
    where collections.sku::text = any( nv.inside );

but that only fills in the first product that's found inside each collections. Why is that? Do I need to do some sort of JOIN to get this to work?

Sample data:

collections

sku    components
10A    {}
20A    {}

products

ordering_id    inside
1234            {10A}
5678            {10A, 20A}

should leave products unchanged but collections should now be

collections

sku    components
10A    {1234,5678}
20A    {5678}

Unfortunately what I get from the SQL that I came up with above is

sku    components
10A    {1234}
20A    {5678}

Best Answer

The following aggregates the products as you want it:

select c.sku, array_agg(p.ordering_id) as ids
from collections c
  join products p on c.sku = any(p.inside)
group by c.sku;

The above returns:

sku | ids        
----+------------
10A | {1234,5678}
20A | {5678}

Now this can be used as the source of an update statement:

update collections 
   set components = t.ids
from (
    select c.sku, array_agg(p.ordering_id) as ids
    from collections c
      join products p on c.sku = any(p.inside)
    group by c.sku
) t
where collections.sku = t.sku;
Related Question