How can I insert a certain range of years and its months in SQL

Question

I need to insert a specific range of years and months into a table so I can relate some climate data to those dates. Just like the table bellow:

year	month
1961	1
1961	2
1961	3
1961	...
1961	12
...	...
2019	12

I am a beginner to SQL so i'm having a hard time coding this. I'm using VSC connected to a PostgresSQL Server. Can anyone help me?

I tried searching for a sollution here in stackoverflow but couldn't find it.

Answer 1

You can use a combination of generate_series() and extract():

INSERT INTO your_table(year, month)
SELECT extract(year from d) as year
    ,   extract(month from d) as month
FROM generate_series('1950-01-01'::timestamp -- start date
         , '2049-12-01' -- end date
         , interval '1 month') g(d);

Just set your start and end date (the timestamp) correct and generate_series() creates the entire list. Extract() gives you the year and the month.

Answer 2

`with yearmonths as ( select EXTRACT('year' FROM gen) _year , EXTRACT('month' FROM gen) _month from 
 generate_series ('1961-01-01'::date,current_date,'1 month'::interval) gen
)
-- insert into ...
select _year, _month from yearmonths`

it's probably easier for beginner to use months like using CTE (the with part) than using generate_series directly

Answer 3

SELECT year, generate_series(1,12) AS "month" 
FROM generate_series(1961,2019) AS "year";

Numbers of years and months you showed can be generated directly, without having to extract them from date/timestamp: db<>fiddle demo.

If you showed how you plan to use these, it might turn out to be more helpful to generate actual date-type dates, not numbers representing them.