Postgres - Insert self referencing sequenced record with INSERT INTO SELECT statement

Question

I have been trying to insert data into a self referencing table while the data has been sourced from other different tables. This row is kind of a root record and needs to have its own reference as a foreign key. For easier understanding, providing the table DDL here -

CREATE TABLE self_refer (
    id        SERIAL  PRIMARY KEY,
    parent_id INTEGER      NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES self_refer(id)
);

If we want to insert data using direct values, it has been a discussed topic here and the below statement works well.

INSERT INTO self_refer
            (parent_id)
VALUES      ((SELECT last_value
              FROM   self_refer_id_seq))

In my case, I need to insert data from other tables and thus the statement would be of INSERT INTO SELECT type. Below are my attempts and they don't work as expected.

INSERT INTO self_refer
            (parent_id)
SELECT CURRVAL('self_refer_id_seq')

and

INSERT INTO self_refer
            (parent_id)
SELECT last_value
FROM   self_refer_id_seq 

The first statement with VALUES inserts data appropriately, but the second and third ones pull the pre-latest data for last_value or currval. First query works well even when used with currval function.

Below are the results where first two records show the insertion via VALUES and the next two are with INSERT INTO SELECT statements.

id|parent_id|
--|---------|
47|       47|
48|       48|
49|       48|
50|       49|

Looking for any help on how to achieve the results like first two rows while keeping the SQL statements similar to the second two.

Update: Accepted answer works, but might not be an ideal solution, read through all the comments on how a better solution was arrived.

Added the statements below for quicker access. Feel free to check out db fiddle if preferred to tweak and try other possibilities.

CREATE TABLE self_refer (
    id        SERIAL  PRIMARY KEY,
    parent_id INTEGER      NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES self_refer(id)
);

✓

INSERT INTO self_refer
            (parent_id)
VALUES      ((SELECT last_value
              FROM   self_refer_id_seq))

1 rows affected

INSERT INTO self_refer
            (parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2

INSERT INTO self_refer
            (parent_id)
VALUES      ((SELECT last_value
              FROM   self_refer_id_seq))

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3

INSERT INTO self_refer
            (parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3
 4 |         4

INSERT INTO self_refer
            (parent_id)
SELECT last_value +1
FROM   self_refer_id_seq 

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3
 4 |         4
 5 |         5

insert into self_refer values(default, (currval('self_refer_id_seq')));

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3
 4 |         4
 5 |         5
 6 |         6

CREATE FUNCTION my_trigger_function()
RETURNS trigger AS '
BEGIN
  IF NEW.parent_id = -1 THEN
    NEW.parent_id := NEW.id;
  END IF;
  return new;
END ' LANGUAGE 'plpgsql'

✓

create trigger test_t
before insert on self_refer
for each row
EXECUTE PROCEDURE my_trigger_function()

✓

INSERT INTO self_refer
            (parent_id)
SELECT -1

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3
 4 |         4
 5 |         5
 6 |         6
 7 |         7

INSERT INTO self_refer
            (parent_id)
SELECT 5

1 rows affected

select * from self_refer;

id | parent_id
-: | --------:
 1 |         1
 2 |         2
 3 |         3
 4 |         4
 5 |         5
 6 |         6
 7 |         7
 8 |         5

db<>fiddle here

Answer 1

A much more common design would allow the column column parent_id to be null in the case of a top level parent. It's also a superior design; at least IMHO. But

insert into self_refer values(default, (currval('self_refer_id_seq')));

Does the trick.

Answer 2

Would something like this be an option:

INSERT INTO self_refer
            (parent_id)
SELECT CURRVAL('self_refer_id_seq')+1;

---

INSERT INTO self_refer
            (parent_id)
SELECT last_value +1
FROM   self_refer_id_seq 

It does work: DEMO