Postgresql – generate a series for this

postgresql

Trying to generate a query where each date must have at least one transect row value of (1,2,3,4,5).

In cases where there is not at least one value of each (1,2,3,4,5) for transect, I want to add it.

So, if the following existed in the table:

|       date | value | transect |
|------------+-------+----------|
| 2019-05-01 |   1.0 |        1 |
| 2019-05-01 |   3.0 |        1 |
| 2019-05-01 |   1.4 |        2 |
| 2019-05-01 |   2.2 |        2 |
| 2019-05-01 |   2.4 |        2 |

I would like to add rows to make the following:

|       date | value | transect |
|------------+-------+----------|
| 2019-05-01 |   1.0 |        1 |
| 2019-05-01 |   3.0 |        1 |
| 2019-05-01 |   1.4 |        2 |
| 2019-05-01 |   2.2 |        2 |
| 2019-05-01 |   2.4 |        2 |
| 2019-05-01 |   0.0 |        3 |
| 2019-05-01 |   0.0 |        4 |
| 2019-05-01 |   0.0 |        5 |

Could this be solved with generate_series?

Best Answer

Do you need something like this?

WITH cte AS ( SELECT dates.date date, series.generate_series transect
              FROM ( SELECT DISTINCT date 
                     FROM datatable ) dates,
                   ( SELECT * 
                     FROM generate_series(1, 5) ) series
            )
SELECT cte.date, COALESCE(datatable.value, 0) AS value, cte.transect
FROM cte
LEFT JOIN datatable ON cte.date = datatable.date
                   AND cte.transect = datatable.transect;
Related Question