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.