How to split single row yearly values into multi row monthly values in PostgreSQL?-CodePudding

I have a table of values from yearly payments like this:

|   id   |   date   |  yearly_payment  |
| ------ | -------- | :--------------: |
| 1      | 06/01/21 | $600             |
| 2      | 06/01/22 | $720             |

What I am trying to achieve is:

|   id   |   date   |  monthly_payment  |
| ------ | -------- | :---------------: |
| 1      | 06/01/21 | $50               |
| 1      | 07/01/21 | $50               |
| 1      | ...      | $50               |
| 1      | 05/01/21 | $50               |
| 2      | 06/01/22 | $60               |
| 2      | 07/01/22 | $60               |
| 2      | ...      | $60               |
| 2      | 05/01/22 | $60               |

I thought I could achieve this through some transformation on a pivot table, but to no avail. This solution gets me close, but I can't quite figure out how to achieve it within Postgres.

CodePudding user response：

Would this work?

select
  y.id, y.date   interval '1 month' * gs.a as date,
  y.yearly_payment / 12 as monthly_payment
from
  yearly_payments y
  cross join generate_series (0, 11) gs (a)

Beware of rounding... if yearly_payment is an integer, then you would want to divide by 12.0 to force a numeric context.