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
?