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
Advertisement
Answer
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 |