Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tableList(
    "unique_id" INT GENERATED BY DEFAULT AS IDENTITY (START WITH 4),
    "next_id" int, 
    "randomCol1" text
);
    
INSERT INTO tableList
    ("next_id", "randomCol1")
VALUES
    (5, 'string 1'),
    (6, 'string 2'),
    (0, 'string 3');

SELECT * FROM tableList;
3 rows affected



unique_id
next_id
randomCol1


4
5
string 1

5
6
string 2

6
0
string 3


WITH select_part AS (
    SELECT next_id
    FROM tableList
    WHERE unique_id = 4
),
insert_part AS (
    INSERT INTO tableList ("next_id", "randomCol1")
        SELECT next_id, 'my new string'
        FROM select_part
    RETURNING unique_id
)
UPDATE tableList t
SET next_id = i.unique_id
FROM (
    SELECT * 
    FROM insert_part
) i
WHERE t.unique_id = 4
1 rows affected

SELECT * FROM tableList


unique_id
next_id
randomCol1


5
6
string 2

6
0
string 3

7
5
my new string

4
7
string 1