Oracle outer join by (+) to Postgres conversion
All those derived tables are useless (even in Oracle).
As far as I can tell, this should be equivalent:
SELECT r1.fibre AS root_edge_sid,
       sp.sid   AS node_sid,
       sp.fid   AS node_fid,
       sp.ftid  AS node_ftid,
       f.sid    AS edge_sid,
       f.fid    AS edge_fid,
       f.ftid   AS edge_ftid
FROM table_a r1
  JOIN table_b sp ON r1.splice_point = sp.sid
  LEFT JOIN table_a r2 ON sp.sid = r2.splice_point
  LEFT JOIN table_c f ON r2.fibre = f.sid
WHERE r1.fibre = 123456
AND r1.fibre &lt;&gt; f.sid;

I think the condition r1.fibre &lt;&gt; f.sid in the WHERE clause was wrong as it turned the outer join back into an inner join.