Insert into a table with a serial field and using its future value

Question

I have the following table:

CREATE TABLE problem (
  id SERIAL,
  title VARCHAR(50),
  author VARCHAR(50),
  path TEXT,
  compiler VARCHAR(20),
  PRIMARY KEY (id)
);

problem.id is an auto-incremented integer. There are ways of doing an insert without knowing its value:

INSERT INTO problem VALUES (DEFAULT, 'Hello World', 'unknown', '/var/www/files/problems', 'Python');
INSERT INTO problem (title, author, path, compiler) VALUES ('Hello World', 'unknown', '/var/www/files/problems', 'Python');

However, I would like to know the problem.id at insert time, so I can append it to path:

/var/www/files/problems/{id}

Where {id} is the problem.id of the inserted problem.

Answer 1

You can use combination of nextval and lastval:

INSERT INTO problem VALUES (
   nextval('problem_id_seq'), 
   'Hello World',
   'unknown', 
   '/var/www/files/problems/' || lastval(), 
   'Python'
);

Call to nextval is exactly what Postgres uses as the default value for SERIAL fields. So it is possible to write the statement without the explicit nextval call:

INSERT INTO problem VALUES (
   DEFAULT, 
   'Hello World',
   'unknown', 
   '/var/www/files/problems/' || lastval(), 
   'Python'
);