insert using subqueries that return a different number of values

Question

Using Postgres, I'm trying to insert into table "sellable_product_categories" ids coming from two different tables (sellables & product_categories).

The first table has multiple values while the second only has one (the id of the product category).

insert into sellable_product_categories (sellable_id, product_category_id)
values (
        (select id from sellables where category = 'Backsplash'),
        (select id from product_categories where name = 'Tile'));

The code generates this error: "ERROR: more than one row returned by a subquery used as an expression" which makes sense since the insert is asymmetrical, but how to resolve this?

The desired result would be something like this:

id	sellable_id	product_category_id
1	17	8
2	20	8
3	35	8
4	64	8
5	89	8
6	92	8

I tried many different permutations, including a left join, but at this point I'm just flat out stumped. Any help would be greatly appreciated.

Answer 1

You can insert the appropriate values directly from a query where you match up the sellables and the product category as required. It appears from your question that you want a cross join on a subset of rows from each table, which can be done like so:

INSERT INTO sellable_product_categories (sellable_id, product_category_id)
    SELECT s.id, p.id
    FROM sellables s
    CROSS JOIN product_categories p 
    WHERE s.category = 'Backsplash'
      AND p.name = 'Tile'