How to convert the the oracle query to PostgreSQL to get the count of table

Question

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

Answer 1

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)