Reference value of serial column in another column during same INSERT
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, theserial
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 ifnextval
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 executednextval
since the current session did.This function requires
USAGE
orSELECT
privilege on the sequence.
lastval
Return the value most recently returned by
nextval
in the current session. This function is identical tocurrval
, except that instead of taking the sequence name as an argument it refers to whichever sequencenextval
was most recently applied to in the current session. It is an error to calllastval
ifnextval
has not yet been called in the current session.This function requires
USAGE
orSELECT
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.
This worked in my test:
INSERT INTO foo (id, ltree) VALUES (DEFAULT, (SELECT last_value from foo_id_seq));
I think there's a race condition there if two INSERTs are happening at the same time, since this references the last sequence value, instead of the current row. I would personally be more inclined to do this (pseudo-code):
my $id = SELECT nextval('foo_id_seq');
INSERT INTO foo (id, ltree) VALUES ($id, '$id');