Search Postgresql Archives

connecting multiple INSERT CTEs to same record?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

I'm looking for a way to insert items to multiple tables (connected with foreign keys) using CTEs.

I found few similar questions on stack-overflow and the mailing list,
but no solution.

Please consider this contrived example:
A table of students, and a table of classes they are in:
====
  create temp table students(
        id serial primary key,
        name varchar);

  create temp table classes(
        id serial primary key,
        student_id int not null references students(id),
        subject varchar);
====

And,
I am given a list of new students and their classes (from an external source, imported into a temp table):

====
create temp table new_data (
        name varchar,
        subject varchar);

insert into new_data values
     ('John','Math'),
     ('Jane','Physics'),
     ('Moe','Science'),
     ('John','English'); -- different student with same name
====


I want to first create new 'students' record, and then create a
corresponding 'classes' record. For that - I need both the newly
assigned 'id', and they corresponding source record from 'new_data'.

A trivial usage of "insert ... returning *" doesn't work - it can't
return columns that were not directly inserted (and the 'subject' string wasn't inserted):

===
  with
     new_students as (
       insert into students(name)
       select name from new_data
       returning *
      )

   insert into classes(student_id, subject)
   select new_students.id,
           -- how to get the subject matching to
           --- the newly inserted student?
           ??????
  from new_students ;
===

I found a work-around, but it relies on a flimsy assumption - that within this transaction, the CTIDs for the 'new_data' and the newly
inserted 'students' rows maintain the same order.
Using this assumption, I calculate a unique value for each row with 'row_number() over (order by ctid)',
assuming that the order stays the same,
then join the tables to match the new student to its 'subject':

===
with
  new_data_with_order as (
       select
           row_number() over (order by ctid) as new_order,
           *
       from new_data )
  ,
  new_students as (
      insert into students(name)
      select name from new_data_with_order
      returning ctid,*
      )
  ,
  new_students_with_order as (
       select
           row_number() over (order by ctid) as new_order,
           *
       from new_students
  )
  ,

  merged_data as (
     -- Is this correct??
     -- it is based on the assumption that the order
     -- of inserted rows into 'students' (and hence, their CTID order)
     -- is the same as the order of the rows in 'new_data'
     -- and their CTID.
     select *
     from new_students_with_order
     join new_data_with_order
     on
       new_students_with_order.new_order =
         new_data_with_order.new_order
  )

  -- for troubleshooting:
  -- select * from merged_data ;

  insert into classes(student_id, subject)
  select
     id, -- merged_data.id is the newly assigned student.id primary key.
     subject
  from merged_data ;

===


So my question is:
Is this assumption about CTID maintaining order (within the transaction) correct? Even when considering concurrency? Even if in strange coincidence 'VACCUUM' is running in parallel to this query?

Or,
Is there another way to achieve this? consider that this is a contrived
example, in my use-case I need to update several tables.
Of course it can be done on the application-level, one-by-one inserting new 'student' then inserting its 'classes' - but I prefer to avoid that.

Sadly, I can't assume the student name is unique, so I can't "join" on it.


Thanks!
 - Assaf Gordon






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux