Geek Answers Handbook

The original value of the sequential column in another column during the same INSERT

I have a table with a primary key SERIAL, as well as an ltree column whose value I want to be a concatenation of these primary keys. eg.

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

I am curious if there is a way to do such an insert in a single request, for example.

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

I'm probably too busy here and trying to do in one request what I should do two each (grouped in a transaction).

+8
sql postgresql sql-insert auto-increment
source share
2 answers

You can use a subquery or a writable CTE to retrieve a value from a sequence once and reuse it:

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

Error correction CTE requires Postgres 9.1 or later.

If you are unsure of the name of the sequence, you can 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" may not be unique for the entire schema in the database, you can qualify it. And if the spelling of any name is non-standard, you need to make a double quote:

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


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

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

  • You can just leave id outside the request, the serial column will be assigned automatically. Irrelevant.

  • Between the lines there should be no race condition. I indicate the manual :

Currval

Returns the last value last received for this sequence in the current session. (An error is reported if nextval has never called this sequence in this session.) Because it returns the local value of the session, it gives a predictable answer: whether or not other sessions performed by nextval after the current session did.

lastval

Returns a value, the last of which was 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 will select the value of the last sequence used by nextval in the current session. This is an error for calling lastval if nextval has not yet been called in the current session.

My bold accent.

But , as @Bernard commented , this may fail. On the other hand, it makes sense: there is no guarantee that the default value is filled (and nextval() is called in the process) before lastval() is called to fill the second ltree column. So stick with the first solution and nextval() .

+7
source share

This worked in my test:

 INSERT INTO foo (id, ltree) VALUES (DEFAULT, (SELECT last_value from foo_id_seq));

I think there is a race condition there if two INSERTs occur simultaneously, as this refers to the last value of the sequence instead of the current line. I personally would be more likely to do this (pseudo-code):

 my $id = SELECT nextval('foo_id_seq'); INSERT INTO foo (id, ltree) VALUES ($id, '$id');
+3
source share

More articles:


All Articles