Re: connecting multiple INSERT CTEs to same record?

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