Postgres Insert: Use newly inserted row's id in another column?

Question

I am designing a table with audit trailing based on the architecture in this article:

https://www.codeproject.com/Articles/105768/Audit-Trail-Tracing-Data-Changes-in-Database

The gist of it is that when you update a row, you just insert a copy of the current row then update the row so you have a history of the states of the data at points in time. Here is an example from the article of what the table looks like:

My question is about the very first insert. When inserting the first row, I need to mark the original_id the same as the id of the row. Can I do that in one statement? Or do I have to insert the new row first then update the original_id column with the new id.

As per the example picture, this is what the insert statement would look like:

begin;

insert into people (status, name, created_date, created_by)
values ('active', 'Julia Singleton', 'today', 'me')
returning id;

// use the returned id to update original_id on the row
// ...

commit;

I'm curious if this can be done in 1 statement.

Answer 1

Using the example shown by the OP, you can specify a default value for original_id column to be the current value of the sequence responsible for generating the id - if it's set up as serial or identity column, you can get it dynamically with pg_get_serial_sequence():

create table people (
  id bigint generated by default as identity primary key,
  original_id bigint not null 
                     default currval(pg_get_serial_sequence('"people"', 'id'))
                     references people(id),
  status text, 
  name text, 
  created_date date, 
  created_by text);

If the table's already in place, you can also alter the column in-place:

alter table people alter column original_id
  set default currval(pg_get_serial_sequence('"people"', 'id'));

Then, when you want original_id to default to id, you just leave it out the insert target column list and it'll be generated the same way id would: demo at db<>fiddle

insert into people (status, name, created_date, created_by)
select 'status_'||g,'name_'||g,(-100*random())::int+date'today','created_by_'||g
from generate_series(1,3)_(g)
returning *;

id	original_id	status	name	created_date	created_by
1	1	status_1	name_1	2023-11-18	created_by_1
2	2	status_2	name_2	2023-10-31	created_by_2
3	3	status_3	name_3	2023-12-20	created_by_3

Here's how I imagine you'd add another version of a row:

insert into people (original_id,status, name, created_date, created_by)
select id,status||'_v2', name||'_v2', 'today', created_by
from people where id=3
returning *;

id	original_id	status	name	created_date	created_by
4	3	status_3_v2	name_3_v2	2024-02-08	created_by_3

Doing that leaves the default insert process unaffected - each row sees the current value of their id-generating sequence. You can also list and assign id and original_id explicitly:

insert into people (id, original_id, status, name, created_date, created_by)
values (nextval(pg_get_serial_sequence('"people"', 'id')),
        currval(pg_get_serial_sequence('"people"', 'id')),
        'active', 'Julia Singleton', 'today', 'me')
returning *;

---

If you wish to use values that were generated this way in an insert statement, you can simply wrap it in a with CTE, then use the output of its returning clause as the source for your other statement:

create table people_salary (
  id bigint generated by default as identity primary key,
  people_id bigint references people(id),
  salary numeric);

insert into people_salary(people_id,salary)
select id,7e4 from people;

with new_versions_of_people as (
  insert into people (original_id, status, name, created_date, created_by)
  select id,'RICH_'||status,'Mr/Mrs_'||name,date'today',created_by||'(lottery)'
  from people where id in (1,7)
  returning id,original_id)
update people_salary
set salary=round(salary^2),
    people_id=new_versions_of_people.id
from new_versions_of_people
where people_id=original_id
returning *;

id	people_id	salary	id	original_id
1	8	4900000000	8	1
7	9	4900000000	9	7

Everyone started off with a 70k salary, then you made new, rich versions of 2 people, which you then made actually rich by squaring their salary.

• Note that the outer returning * gives you both the full result of the update, as well as the insert..returning fields it was based on. It's good practice to alias all these fields adequately - otherwise, you end up with multiple columns named the same.
• The select id refers to the id found by from people where... that will go into original_id. The later returning id will refer to the field generated by this insert, because id wasn't targeted.
• In the outer update, where people_id=original_id tells it which row to update before it overwrites its value by doing people_id=new_versions_of_people.id