SQL query to get the leaf record in a parent-child relationship
This was written while exploring various ways, so if you just want the best solution, see at bottom, otherwise read on to follow the train of thoughts and the progressive refinements.

If you can guarantee order in our IDs (that is new_id > old_id for any renewal, if that is not true see below for enhanced method) then you can do:

WITH RECURSIVE renewals AS (
    SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1 
    UNION ALL
    SELECT pr.old_id, pr.new_id FROM ProductRenewals pr 
        INNER JOIN renewals r ON pr.old_id=r.new_id)
SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1


Replace the 1 in old_id = 1 by the value you want to query for.
This will return either nothing (cases 3 and 4) or the id (cases 1 and 2) as such:

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 2 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 3 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 4 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)


If you replace the latest SELECT by SELECT MAX(new_id) FROM renewals you will get either an id or NULL. You could also do something like SELECT COALESCE(MAX(new_id), 0)  FROM renewals, or reuse the id you used as input instead of 0 to match your first design.

Postgresql documentation on recursive CTE is at https://www.postgresql.org/docs/8.4/static/queries-with.html and you can find a canonical example with employees and managers at http://www.postgresqltutorial.com/postgresql-recursive-query/

With as slight change of the above, you do not need to hold any condition on how the id are ordered, just store a counter at each "iteration" step of the recursion, like that:

test=# WITH RECURSIVE renewals AS (SELECT 1 iter, old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;


(do not change the first 1, it is the starting counter completely not related to your id values, even if any value would fit here in fact)

In fact by slightly elaborating on this you can use this final form that both allows you to get exactly the result as expected (specially in case of non renewals) and also do not care about ordering at all. See:

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 1) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 2) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      2
(1 row)

rest=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 3) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 4) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)