Want to migrate query from oracle to postgresql

Question

Query:

Select n 
  from tab1
 where id in (select id 
                from tab2
               start with id=5
             connect by prior parentid=tab2.id) 
   and n like ‘N_%’ 

I tried the below query but its not giving me the correct output. So i need help here

With recursive a as (
Select n from tab1 where n like ‘N_%’
Union all
Select id from tab2 join tab2.id=tab1.id)
Select * from a;

Query is working but output defers with oracle

Answer 1

You need to recurse first and then filter on the result (rather than trying to join and filter simultaneously):

WITH RECURSIVE a AS (
  SELECT id, parentid
  FROM   tab2
  WHERE  id = 5 -- START WITH clause
UNION ALL
  SELECT tab2.id, tab2.parentid
  FROM   tab2
         INNER JOIN a
         ON a.parentid = tab2.id -- CONNECT BY clause
)
SELECT n
FROM   tab1
WHERE  id IN (SELECT id FROM a)
AND    n LIKE 'N_%';

Which, for the sample data:

CREATE TABLE tab1 (n VARCHAR(20), id INT);
CREATE TABLE tab2 (id INT, parentid INT);

INSERT INTO tab1 (n, id)
  SELECT 'NX01', 1 UNION ALL
  SELECT 'XX02', 2 UNION ALL
  SELECT 'NY03', 3 UNION ALL
  SELECT 'NX04', 4;

INSERT INTO tab2 (id, parentid)
  SELECT 5, 1 UNION ALL
  SELECT 1, 2 UNION ALL
  SELECT 2, 4 UNION ALL
  SELECT 4, NULL;

Outputs:

n
NX01
NX04

PostgreSQL fiddle Oracle fiddle