On 2021-10-19 1:13 p.m., David G. Johnston wrote:
>
> Instead of assigning a unique identifier to student after inserting it
> into the table, assign the identifier first. Generally this is done by
> using “nextval()”
Aha! Such elegant solution!
Seems obvious in hindsight, but I just couldn't figure it out nor find
any mentions to it online.
For others who might stumble upon this thread in the future,
the solution becomes:
====
with
-- pre-assign unique IDs for each record,
-- without inserting them to the table yet.
new_data_with_ids as (
select
nextval(pg_get_serial_sequence('students','id'))
as new_student_id,
new_data.*
from new_data )
,
-- Now insert the new names, with their pre-assigned IDs
new_students as (
insert into students(id,name)
select new_student_id, name
from new_data_with_ids
returning * -- optional
)
,
-- And use the IDs for other tables, too
new_classes as (
insert into classes(student_id, subject)
select new_student_id, subject
from new_data_with_ids
returning * --optional
)
-- return the new IDs with the data
select * from new_data_with_ids ;
===
Thank you!
Regards,
- Assaf Gordon