title: NOCYCLE in Postgres
body: <p>I have a Oracle query with a <code>NOCYCLE</code> clause which I have to translate to Postgres:</p>
  
  <pre><code>SELECT FG_ID,CONNECT_BY_ROOT FG_ID as Parent_ID  
  FROM FG t
  START WITH t.Parent_filter_group_id is null 
  CONNECT BY NOCYCLE PRIOR t.FILTER_GROUP_ID = t.PARENT_FILTER_GROUP_ID 
  </code></pre>
  
  <p>I have converted this one with the help of the question and answer in 
  <a href="https://stackoverflow.com/questions/22296248/connect-by-root-equivalent-in-postgres">connect_by_root equivalent in postgres</a></p>
  
  <p>as </p>
  
  <pre><code>with recursive fg_tree as (
  select FG_ID,
         FG_ID as fg
  from  FG
  where Parent_filter_group_id is null 
  
  union all 
  select c.FG_ID,
  p.fg
  from FG c join fg_tree p on p.FG_ID = PARENT_FILTER_GROUP_ID
  )
  select * from fg_tree
  order by FG_ID
  </code></pre>
  
  <p>but in this there is no clause for <code>NOCYCLE</code> if the parent is also one of the children then this query will return error.</p>