PostgreSQL view adding missing rows

Question

I need a view definition to augment the following data for table 'segments'

segmentNo fileId  c1    c2    c3
---------------------------------
3         1       1.1   2.2   3.3
5         1       1.1   2.2   3.3
2         2       1.1   2.2   3.3
4         2       1.1   2.2   3.3

with missing "segments", so that the caller of the view will see

segmentNo fileId  c1    c2    c3
----------------------------------
0         1       NULL  NULL  NULL
1         1       NULL  NULL  NULL
2         1       NULL  NULL  NULL
3         1       1.1   2.2   3.3
4         1       NULL  NULL  NULL
5         1       1.1   2.2   3.3
0         2       NULL  NULL  NULL
1         2       NULL  NULL  NULL
2         2       1.1   2.2   3.3
3         2       NULL  NULL  NULL
4         2       1.1   2.2   3.3

So, the maximum segmentNo for each fileId (a foreign key) has to be determined and rows for missing "segments" (starting with 0) have to be added. Is it possible to write an SQL view (PostgreSQL >= 13) to accomplish this?

In most use cases, views are used for presenting data in different ways, but I wonder if it is also possible to complement data.

Answer 1

Use aggregation with generate_series() to get all segmentNos of each fileId and then LEFT join the results to the table:

CREATE VIEW view_name AS
WITH cte AS (
  SELECT generate_series(0, MAX(segmentNo)) AS segmentNo,
         fileId
  FROM segments
  GROUP BY fileId
)
SELECT c.segmentNo, c.fileId,
       s.c1, s.c2, s.c3
FROM cte c LEFT JOIN segments s
ON s.fileId = c.fileId AND s.segmentNo = c.segmentNo;

See the demo.