I’m trying to use CYCLE detection in PostgreSQL to avoid circular nesting of groups but cannot figure it out.

I have the following PostgreSQL 16 table:

CREATE TABLE groups (
  id serial PRIMARY KEY,
  name text NOT NULL
);

INSERT INTO groups (name)
VALUES
  ('group1'),
  ('group2'),
  ('group3'),
  ('group4');

SELECT * FROM groups;

Output:

And the following junction table:

CREATE TABLE related_groups_self (parent_id int, child_id int);

INSERT INTO related_groups_self (parent_id, child_id) 
VALUES
    (1, 2),
    (2, 3),
    (3, 4);

SELECT * FROM related_groups_self

Output:

If my app user was to attempt to add group 4 as the parent of group 1; (4, 1), how can I detect this using CYCLE detection?

I want to avoid inserting the tuple until after the check has verified as safe from circular nesting, so the incorrect entry won’t exist in the junction table already.

I’m no DBA, and I’ve been banging my head against this issue using multiple different methods from various blog posts on the subject with very little success. Here’s the latest effort, although obviously not working:

WITH RECURSIVE tree AS (
    SELECT child_id, 1 AS level
    FROM related_groups_self rgs
    WHERE rgs.parent_id = 1
  UNION
    SELECT rgs.child_id, tree.level + 1
    FROM tree
    JOIN related_groups_self as rgs ON(tree.child_id = rgs.parent_id)
) CYCLE child_id SET is_cycle USING cycle_path
SELECT * FROM tree;

Any help would be appreciated