sql - Reference value of serial column in another column during same INSERT

Question

I have a table with a SERIAL primary key, and also an ltree column, whose value I want to be the concatenation of those primary keys. e.g.

id | path
----------
1    1
2    1.2
3    1.2.3
4    1.4
5    1.5

I'm curious if there's a way to do such an insert in one query, e.g.

INSERT INTO foo (id, ltree) VALUES (DEFAULT, THIS.id::text)

I'm probably overreaching here and trying to do in one query what I should be doing in two (grouped in a transaction).

Answer 1

You could use a CTE to retrieve the value from the sequence once and use it repeatedly:

WITH cte AS (
   SELECT nextval('foo_id_seq') AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   cte;

The CTE with a data-modifying command requires Postgres 9.1 or later.

If you are not sure about the name of the sequence, use pg_get_serial_sequence() instead:

WITH i AS (
   SELECT nextval(pg_get_serial_sequence('foo', 'id')) AS id
   )
INSERT INTO foo (id, ltree)
SELECT id, '1.' || id
FROM   i;

If the table name "foo" might not be unique across all schemas in the DB, schema-qualify it. And if the spelling of any name is non-standard, you have to double-quote:

pg_get_serial_sequence('"My_odd_Schema".foo', 'id')

---

Quick tests indicated @Mark's idea with lastval() might work too:

INSERT INTO foo (ltree) VALUES ('1.' || lastval());

• You can just leave id out of the query, the serial column will be assigned automatically. Makes no difference.

• There shouldn't be a race condition between rows. I quote the manual:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

This function requires USAGE or SELECT privilege on the sequence.

lastval

Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it refers to whichever sequence nextval was most recently applied to in the current session. It is an error to call lastval if nextval has not yet been called in the current session.

This function requires USAGE or SELECT privilege on the last used sequence.

Bold emphasis mine.

But, as @Bernard commented, it can fail after all: there is no guarantee that the default value is filled (and nextval() called in the process) before lastval() is called to fill the 2nd column ltree. So stick with the first solution and nextval() to be sure.