oracle sql to postgresql level query

Question

I have an Oracle Sql query which gives a list of months in a financial year. I want to convert to postgresql as it doesn't have level

select case
         when to_char(add_months(sysdate , -1), 'MM') >= 4 then
          to_char(add_months(sysdate , -1), 'YYYY') || '-' ||
          to_char(to_number(to_char(add_months(sysdate , -1), 'YYYY')) + 1)
         else
          to_char(to_number(to_char(add_months(sysdate , -1), 'YYYY')) - 1) || '-' ||
          to_char(add_months(sysdate , -1), 'YYYY')
       end FY,
       to_char(level, '00') MNTH
  from dual
connect by level <=12

Possible duplicate of [What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?](https://stackoverflow.com/questions/24898681/what-is-the-equivalent-postgresql-syntax-to-oracles-connect-by-start-with) — atokpas, Nov 28 '17 at 06:45
You want to generate a series of 12 months for a fiscal year suggest you look here: https://stackoverflow.com/a/7450601/2067753 — Paul Maxwell, Nov 28 '17 at 06:50

Paul Maxwell · Accepted Answer · 2017-11-28T07:28:53.450

select 
    case when date_part('month',current_date) >= 4 then
              concat(date_part('year', current_date), '-', date_part('year',current_date)+1)
         else
              concat(date_part('year', current_date)-1, '-', date_part('year',current_date))
    end FY
    , lpad(date_part('month',d)::varchar,2,'0') MNTH
from (select DATE '2008-01-01' + (interval '1' month * generate_series(0,11)) d ) y

current_date (instead of sysdate)
+ Interval 'n Month' (instead of add_months)
generate series (instead of connect by )
date_part('month',... to get the month number (instead of to_char(...,'MM') )
concat() for concatenations (looks after type conversions too)

sample result:

    FY  MNTH
1   2017-2018    01
2   2017-2018    02
3   2017-2018    03
4   2017-2018    04
5   2017-2018    05
6   2017-2018    06
7   2017-2018    07
8   2017-2018    08
9   2017-2018    09
10  2017-2018    10
11  2017-2018    11
12  2017-2018    12

oracle sql to postgresql level query

1 Answers1