Insert self-referencing entries in Postgresql

Question

Given the following table in PostgreSQL, how do I insert a record that references itself?

CREATE TABLE refers ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) ); 

In the examples that I find on the Internet, parent_id is allowed to be NULL, and then use the trigger to update it. I would rather upgrade with one shot, if possible.

Answer 1

You can select last_value from the sequence that is automatically created when using the serial number:

 create table test ( id serial primary key, parent integer not null, foreign key (parent) references test(id) ); insert into test values(default, (select last_value from test_id_seq)); insert into test values(default, (select last_value from test_id_seq)); insert into test values(default, (select last_value from test_id_seq)); select * from test; id | parent ----+-------- 1 | 1 2 | 2 3 | 3 (3 rows) 

And it seems even easier:

 insert into test values(default, lastval()); 

Although I do not know how this will work when using multiple sequences ... I searched for it; lastval () returns the last value returned or set with the last nextval or setval call of any sequence, so the following may cause you problems:

 create table test ( id serial primary key, foo serial not null, parent integer not null, foreign key (parent) references test(id) ); select setval('test_foo_seq', 100); insert into test values(default, default, lastval()); ERROR: insert or update on table "test" violates foreign key constraint "test_parent_fkey" DETAIL: Key (parent)=(101) is not present in table "test". 

However, it would be nice:

 insert into test values(default, default, currval('test_id_seq')); select * from test; id | foo | parent ----+-----+-------- 2 | 102 | 2 (1 row) 

Answer 2

The main question is: why do you want to insert a record related to yourself?

The scheme is similar to the standard adjacency list - one of the methods for implementing trees in a relational database.

The thing is, in most cases you just have parent_id NULL for the top-level element. It is actually much easier to handle.