Inserting self-referential records in Postgresql

Question

Given the following table in PostgreSQL, how do I insert a record which refers to 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)
);

The examples I'm finding on the Web have been allowed the parent_id to be NULL and then use a trigger to update it. I'd rather update in one shot, if possible.

Answer 1

The main question is - why would you want to insert record which relates to itself?

Schema looks like standard adjacency list - one of methods to implement trees in relational database.

The thing is that in most cases you simply have parent_id NULL for top-level element. This is actually much simpler to handle.

Answer 2

You can select last_value from the sequence, that is automatically created when you use type serial:

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 the following even simpler seems to work as well:

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

Though I don't know how this would work when using multiple sequences... I looked it up; lastval() returns the last value returned or set with the last nextval or setval call to any sequence, so the following would get you in trouble:

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 the following would be okay:

insert into test values(default, default, currval('test_id_seq'));

select * from test;
 id | foo | parent
----+-----+--------
  2 | 102 |      2
(1 row)