I’m on Postgres 15.1 and have a node table as follows:

CREATE TABLE node (
    id TEXT PRIMARY KEY,
    is_skippable BOOL,
    prev TEXT REFERENCES node(id),
    "next" TEXT REFERENCES node(id)
);

This table represents a series of doubly linked lists, but on certain occasions for certain queries, I want to remove is_skippable nodes from the list and link around them.

For example, if I had the following data seeded in the DB, where there’s one linked list of A1 <-> A2 <-> A3 (with A2 skippable) and one of B1 <-> B2:

INSERT INTO node VALUES 
  ('A1', FALSE, NULL, 'A2'), 
  ('A2', TRUE, 'A1', 'A3'), 
  ('A3', FALSE, 'A2', NULL),
  ('B1', FALSE, NULL, 'B2'), 
  ('B2', FALSE, 'B1', NULL);

For certain queries I want to link around is_skippable nodes. So if I queried this full table, the result set I’m looking for is:

Note that A2 isn’t in the result set and A2‘s pointers have been rewritten to the node before and after it.

Is there a well-supported way to do this in Postgres? I’ve seen answers for simple linked lists elsewhere on StackOverflow (like Linked list concept in postgresql), but they don’t entail having to rewrite pointers. Thanks!