What Is the Equivalent Postgresql Syntax to Oracle's Connect by ... Start With

What is the equivalent PostgreSQL syntax to Oracle's CONNECT BY ... START WITH?

Use a RECURSIVE CTE in Postgres:

WITH RECURSIVE cte AS (
   SELECT key, value, 1 AS level
   FROM   taxonomy
   WHERE  key = 0

   UNION  ALL
   SELECT t.key, t.value, c.level + 1
   FROM   cte      c
   JOIN   taxonomy t ON t.taxHier = c.key
   )
SELECT value
FROM   cte
ORDER  BY level;

Details and links to documentation in my previous answer:

• Does PostgreSQL have a pseudo-column like "LEVEL" in Oracle?

Convert oracle Sql to postgresql with CONNECT BY level

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

ORACLE connect by to POSTGRESQL conversion

Not sure what the group by nbr is supposed to achieve - as far as I can tell this serves no purpose.

The convoluted connect by level in Oracle can be replaced with a simple generate_series() in Postgres.

So the following will generate 11 timestamp values from 2021-08-11 01:30:00 to 2021-08-11 06:30:00:

select g.dt
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

This can then be aggregated back into a string using string_agg()

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), 10 as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') as g(dt)

If you need the number of rows generated, you can use the with ordinality clause to get that:

select string_agg(to_char(dt, 'dd/mm/yyyy hh24:mi:ss'), ' '), max(idx) as nbr
from generate_series(timestamp '2021-08-11 01:30:00', 
                     timestamp '2021-08-11 06:30:00', 
                     interval '30 minute') with ordinality as g(dt,idx)

Connect by level in Postgres on a sequence

The direct translation of that query is to use generate_series() and nextval()

select nextval('id_seq')
from generate_series(1,3);

This will advance the sequence three times.

---

If however the goal is to set a specific value for an existing sequence (which requires such a hack in Oracle), just use setval():

select setval('id_seq', 3);

SYS_CONNECT_BY_PATH and START WITH/CONNECT BY PostgreSQL Equivalent

Use a recursive common table expression:

with recursive tree as (
  select wtn, 
         resp_tcsi as tcsi_codes
   from data
   where rn = 1 -- this is the "start with" part

   union all

   select ch.wtn, 
          p.tcsi_codes||','||ch.resp_tcsi 
   from data as ch
    join tree p 
      on ch.rn -1 = p.rn -- this is the "connect by" part
     and ch.wtn = p.wtn
)
select *
from tree;

oracle sql to postgresql level query

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

---

Related Topics