Convert oracle Sql to postgresql with "CONNECT BY level"

Question

as "CONNECT BY level" keywords is not supported on PostgreSQL i couldn't translate below sql to PostgreSQL

Please advise how can I rewrite below oracle SQL on PostgreSQL :

SELECT
    TO_DATE('01/01/2019','MM/DD/YYYY') + level - 1 AS days,
    level AS level1
FROM
    dual
CONNECT BY
    level <= (
        SELECT
            abs(TO_DATE('01/01/2021','MM/DD/YYYY') - TO_DATE('01/10/2021','MM/DD/YYYY') )
        FROM
            dual
        WHERE
            'Daily' = 'Daily'
    )

result :

01-JAN-19   1
02-JAN-19   2
03-JAN-19   3
04-JAN-19   4
05-JAN-19   5
06-JAN-19   6
07-JAN-19   7
08-JAN-19   8
09-JAN-19   9

Thanks

GMB · Accepted Answer · 2020-09-01 22:32:01Z

2

Postgres has a handy function called generate_series(), which makes this kind of task easy:

select dt::date, rn
from generate_series('2019-01-01'::date, '2019-01-09'::date, '1 day') 
    with ordinality as d(dt, rn)

I don't see the point for the obsfucated logic in the connect by clause of the Oracle query; the scalar subquery always evaluates as 9.

Demo on DB Fiddlde:

dt         | rn
:--------- | -:
2019-01-01 |  1
2019-01-02 |  2
2019-01-03 |  3
2019-01-04 |  4
2019-01-05 |  5
2019-01-06 |  6
2019-01-07 |  7
2019-01-08 |  8
2019-01-09 |  9

answered Sep 1, 2020 at 22:32

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belayer Over a year ago

Agreed the Oracle is obscure, but lets not think it is Oracle's doing, but the author. The Oracle could have been "select date '2019-01-01' + level - 1 days, level rn from dual connect by level <= 9;" Just as the Postgres version could have been the almost identical "select date '2019-01-01' + level - 1, level rn from generate_series(1,9) gn(level);" I do prefer your query but point is do not blame Oracle because the author writes poor sql.

GMB Over a year ago

@Belayer: sure. My comment was not referring to Oracle, but to the logic of the query itself (as written by its original author).

Collectives™ on Stack Overflow

Convert oracle Sql to postgresql with "CONNECT BY level"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related