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:


sku    components
10A    {}
20A    {}


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

should leave products unchanged but collections should now be


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}
  • Please, add some sample data and the desired result.
    – McNets
    Commented Jun 17, 2017 at 18:06
  • Ok, added them.
    – Nadreck
    Commented Jun 18, 2017 at 8:20

1 Answer 1


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;
  • I figured it had something to do with a JOIN. My JOIN-fu is weak.
    – Nadreck
    Commented Jun 18, 2017 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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