I'm a beginner in postgresql (and in SQL in general), I thank you in advance for your help.
I'm trying to create a procedure to insert rows in a table with the date column (start_date) being incremented by another column (number_months). The increment starts by zero and finish at my value. For example if my start_date is 2021-01-01 and number_months=1, then I want to have two lines, one with new_date = 2021-01-01 + 0 months hence 2021-01-01, the second one with new_date = 2021-01-01 + 1 month hence 2021-02-01.
Below is a sample of my data:
| ID_OPERATION | START_DATE | NUMBER_MONTHS | AMOUNT |
|---|---|---|---|
| 01234 | 2121-01-01 | 1 | 15000 |
| 02345 | 2022-11-01 | 2 | 10000 |
Below is what I want:
| ID_OPERATION | NEW_DATE | AMOUNT |
|---|---|---|
| 01234 | 2121-01-01 | 15000 |
| 01234 | 2121-02-01 | 15000 |
| 02345 | 2022-11-01 | 10000 |
| 02345 | 2022-12-01 | 10000 |
| 02345 | 2023-01-01 | 10000 |
Can you help me doing this?
To do this on the fly without adding rows using generate_series:
CREATE TABLE series_test (
ID_OPERATION varchar,
START_DATE date,
NUMBER_MONTHS integer,
AMOUNT numeric
);
INSERT INTO series_test
VALUES ('01234', '2121-01-01', 1, 15000),
('02345', '2022-11-01', 2, 10000);
SELECT
id_operation,
generate_series(start_date, start_date + (number_months::varchar || ' month')::interval, '1 month'::interval)::date AS new_date,
amount
FROM
series_test;
id_operation | new_date | amount
--------------+------------+--------
01234 | 01/01/2121 | 15000
01234 | 02/01/2121 | 15000
02345 | 11/01/2022 | 10000
02345 | 12/01/2022 | 10000
02345 | 01/01/2023 | 10000
SELECT
id_operation,
generate_series(start_date, start_date + (number_months::varchar || ' month')::interval, '1 month'::interval)::date AS new_date,
amount
FROM
series_test
WHERE
id_operation = '02345';
id_operation | new_date | amount
--------------+------------+--------
02345 | 11/01/2022 | 10000
02345 | 12/01/2022 | 10000
02345 | 01/01/2023 | 10000