Problem

(Note: I have added potential solution to the bottom of the post)

I don't know what's wrong with the approach that I currently have, but when I try to run it dbeaver just instantly quits.

I think it's because I want to insert into the same table, but a different column, so it's creating a recursive error.

To be clear - when data is inserted into the tables x column I want to insert it into the y column at the same time with some alteration. In this example the alteration is just x + 1.

Attempt at a solution:

DROP TABLE IF EXISTS tbl; 
CREATE TABLE tbl (
    x integer,
    z integer
);

INSERT INTO tbl(x)
SELECT x FROM generate_series(1, 5) AS t(x); 
ALTER TABLE tbl
ADD COLUMN y integer;

CREATE OR REPLACE FUNCTION copy_to_new_column() RETURNS TRIGGER AS
$BODY$
BEGIN
    INSERT INTO
    tbl(y)
    VALUES(NEW.x + 1);
    RETURN new;
END;
$BODY$
language plpgsql;

CREATE OR REPLACE TRIGGER trig_copy_to_new
AFTER INSERT ON tbl 
FOR EACH ROW 
EXECUTE 
PROCEDURE 
copy_to_new_column(); 

-- would want to have a row (x, y, z) with (22, 23, 91) after this insert
INSERT INTO tbl(x, z) VALUES 
(22, 91)
;

It makes sense that there's a recursive error, as the function is inserting into the same table that there's a INSERT trigger on, but I don't know how else to go about this.

To be explicit

• Enter a row with integers a, b into the columns x, z
• The trigger will cause the row a, a+1, b to be inserted into columns x, y, z

Edit

Here's an approach that might work:

DROP TABLE IF EXISTS tbl; 
CREATE TABLE tbl (
    x integer, 
    y integer, 
    z integer
);

INSERT INTO tbl(x, z)
SELECT x, x + cast(3 * random() AS integer) FROM generate_series(1, 2) AS t(x); 


CREATE OR REPLACE FUNCTION copy_column_on_insert()
RETURNS TRIGGER AS $$
BEGIN
    NEW.y := NEW.x + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER copy_column_on_insert_trigger
BEFORE INSERT ON tbl
FOR EACH ROW
EXECUTE PROCEDURE copy_column_on_insert();

INSERT INTO tbl(x, z)
VALUES 
(12, 22)
;

SELECT * FROM tbl;