Make sure every distinct value of Column1 has a row with every distinct value of Column2, by populating a table with 0s - postgresql

Question

Here's a crude example I've made up to illustrate what I want to achieve:

table1:

| Shop | Product | QuantityInStock | 
| a    | Prod1   | 13              | 
| a    | Prod3   | 13              | 
| b    | Prod2   | 13              | 
| b    | Prod3   | 13              | 
| b    | Prod4   | 13              | 

table1 becomes:

| Shop | Product | QuantityInStock | 
| a    | Prod1   |   13            | 
| a    | Prod2   |    0            | -- new 
| a    | Prod3   |   13            | 
| a    | Prod4   |    0            | -- new 
| b    | Prod1   |    0            | -- new 
| b    | Prod2   |   13            | 
| b    | Prod3   |   13            | 
| b    | Prod4   |   13            | 

In this example, I want to represent every Shop/Product combination

every Shop {a,b} to have a row with every Product {Prod1, Prod2, Prod3, Prod4}

QuantityInStock=13 has no significance, I just wanted a placeholder number :)

Answer 1

Use a calendar table cross join approach:

SELECT s.Shop, p.Product, COALESCE(t1.QuantityInStock, 0) AS QuantityInStock
FROM (SELECT DISTINCT Shop FROM table1) s
CROSS JOIN (SELECT DISTINCT Product FROM table1) p
LEFT JOIN table1 t1
    ON t1.Shop = s.Shop AND
       t1.Product = p.Product
ORDER BY
    s.Shop,
    p.Product;

The idea here is to generate an intermediate table containing of all shop/product combinations via a cross join. Then, we left join this to table1. Any shop/product combinations which do not have a match in the actual table are assigned a zero stock quantity.