-1

I want to convert the mentioned oracle query to PostgreSQL for getting the count of table

select count(*) from student connect by prior std_id=std_roll
Nayan Arora
  • 79
  • 1
  • 3
  • Your Oracle query doesn't make sense. A `connect by` **without** a `start with` seems wrong. And _with_ a `start with` it would essentially be the same as `select count(*) from student` –  Jan 05 '17 at 13:37
  • http://stackoverflow.com/q/41302331/2235885 exact duplicate by same author. – joop Jan 05 '17 at 14:12

1 Answers1

0

Oracle's connect by can be re-written as a a recursive common table expression in standard SQL (and Postgres):

with recursive tree as (
  select std_id, std_roll
  from student
  where std_roll is null --<< I assume you are missing a START WITH in Oracle
  union all
  select c.std_id, c.std_roll
  from student c
    join tree p on p.std_id = c.std_roll
)
select count(*)
from tree;

This however does not really make sense. The above is a complicated way of writing select count(*) from student (assuming there is a foreign key between std_roll and std_id)