PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    `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

    Login or Signup to reply.
  3. 0

    This generates all your years, and in each, it spawns the 12 months. DB<>Fiddle demo:

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

    If you just need numbers of years and months, you don’t need the date/timestamp that’s normally useful to account for varying month lengths, days of week, leap years, but 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search