0

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?

0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.