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?

1

1 Answer 1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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