How to use nextval() to insert a row containing its own ID in a string

Question

I have a table that contains a column with a string containing its own ID. How can I insert a new line using a single SQL statement?

id| URL
--|----------------------
 1|"http://example.com/1"
 2|"http://example.com/2"
 3|"http://example.com/3"

I need something like

NEXT_ID = SELECT nextval('table_name_id_seq');
insert into table_name (id, name) values (NEXT_ID, 'http://example.com/' + NEXT_ID) returning *

https://www.db-fiddle.com/f/3NwLNBirN7mHKpDk9NyHSy/1

Answer 1

One option is to select the next serial in a subquery first:

insert into test(id, url)
select id, 'http://example.com/' || id::text
from (select nextval('test_id_seq') as id) x;

---

You could also use a computed column instead would make for cleaner insert code:

create table test(
  id SERIAL,
  url text,
  new_url text generated always as (url || id::text) stored
);

insert into test(url) values ('http://example.com/');

Gives you:

| id  | url                 | new_url              |
| --- | ------------------- | -------------------- |
| 1   | http://example.com/ | http://example.com/1 |