Insert into table, return id and then insert into another table with stored id

Question

I have the following three tables: Please note that the below DDL came models generated by Django then grabbed out of Postgresql after they were created. So modifying the tables is not an option.

CREATE TABLE "parentTeacherCon_grade"
(
    id INTEGER PRIMARY KEY NOT NULL,
    "currentGrade" VARCHAR(2) NOT NULL
);
CREATE TABLE "parentTeacherCon_parent"
(
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    grade_id INTEGER NOT NULL
);
CREATE TABLE "parentTeacherCon_teacher"
(
    id INTEGER PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL
);
CREATE TABLE "parentTeacherCon_teacher_grade"
(
    id INTEGER PRIMARY KEY NOT NULL,
    teacher_id INTEGER NOT NULL,
    grade_id INTEGER NOT NULL
);
ALTER TABLE "parentTeacherCon_parent" ADD FOREIGN KEY (grade_id) REFERENCES "parentTeacherCon_grade" (id);
CREATE INDEX "parentTeacherCon_parent_5c853be8" ON "parentTeacherCon_parent" (grade_id);
CREATE INDEX "parentTeacherCon_teacher_5c853be8" ON "parentTeacherCon_teacher" (grade_id);
ALTER TABLE "parentTeacherCon_teacher_grade" ADD FOREIGN KEY (teacher_id) REFERENCES "parentTeacherCon_teacher" (id);
ALTER TABLE "parentTeacherCon_teacher_grade" ADD FOREIGN KEY (grade_id) REFERENCES "parentTeacherCon_grade" (id);
CREATE UNIQUE INDEX "parentTeacherCon_teacher_grade_teacher_id_20e07c38_uniq" ON "parentTeacherCon_teacher_grade" (teacher_id, grade_id);
CREATE INDEX "parentTeacherCon_teacher_grade_d9614d40" ON "parentTeacherCon_teacher_grade" (teacher_id);
CREATE INDEX "parentTeacherCon_teacher_grade_5c853be8" ON "parentTeacherCon_teacher_grade" (grade_id);

My Question is: How do I write an insert statement (or statements) where I do not have keep track of the IDs? More specifically I have a teacher table, where teachers can teach relate to more than one grade and I am attempting to write my insert statements to start populating my DB. Such that I am only declaring a teacher's name, and grades they relate to.

For example, if I have a teacher that belong to only one grade then the insert statement looks like this.

INSERT INTO "parentTeacherCon_teacher" (name, grade_id) VALUES ('foo bar', 1  );

Where grades K-12 are enumerated 0,12

But Need to do something like (I realize this does not work)

INSERT INTO "parentTeacherCon_teacher" (name, grade_id) VALUES ('foo bar', (0,1,3)  );

To indicate that this teacher relates to K, 1, and 3 grades

leaving me with this table for the parentTeacherCon_teacher_grade

+----+------------+----------+
| id | teacher_id | grade_id |
+----+------------+----------+
|  1 |          3 |        0 |
|  2 |          3 |        1 |
|  3 |          3 |        3 |
+----+------------+----------+

This is how I can currently (successfully) insert into the Teacher Table.

INSERT INTO public."parentTeacherCon_teacher" (id, name) VALUES (3, 'Foo Bar');

Then into the grade table

INSERT INTO public.parentTeacherCon_teacher_grade (id, teacher_id, grade_id) VALUES (1, 3, 0);
INSERT INTO public.parentTeacherCon_teacher_grade (id, teacher_id, grade_id) VALUES (2, 3, 1);
INSERT INTO public.parentTeacherCon_teacher_grade (id, teacher_id, grade_id) VALUES (3, 3, 3);

A bit more information. Here is a diagram of the database

Other things I have tried.

WITH i1 AS (INSERT INTO "parentTeacherCon_teacher" (name) VALUES ('foo bar')
RETURNING id) INSERT INTO "parentTeacherCon_teacher_grade"
  SELECT
    i1.id
    , v.val
  FROM i1, (VALUES (1), (2), (3)) v(val);

Then I get this error.

[2016-08-10 16:07:46] [23502] ERROR: null value in column "grade_id" violates not-null constraint

Detail: Failing row contains (6, 1, null).

Why do you want to do it in the same statement? Use stored functions to perform the complex data manipulations. — Abelisto
– Abelisto, Commented Aug 10, 2016 at 22:44
Because I do not know the id of the teacher when I insert. I do not want to keep track of them. — RandomNumberFun
– RandomNumberFun, Commented Aug 10, 2016 at 22:47

Gordon Linoff · Accepted Answer · 2016-08-11 02:10:08Z

1

If you want to insert all three rows in one statement, you can use:

INSERT INTO "parentTeacherCon_teacher" (name, grade_id) 
    SELECT 'foo bar', g.grade_id
    FROM (SELECT 0 as grade_id UNION ALL SELECT 1 UNION ALL SELECT 3) g;

Or, if you prefer:

INSERT INTO "parentTeacherCon_teacher" (name, grade_id) 
    SELECT 'foo bar', g.grade_id
    FROM (VALUES (0), (2), (3)) g(grade_id);

EDIT:

In Postgres, you can have data modification statements as a CTE:

WITH i as (
      INSERT INTO public."parentTeacherCon_teacher" (id, name)
          VALUES (3, 'Foo Bar')
          RETURNING *
     )
INSERT INTO "parentTeacherCon_teacher" (name, teacher_id, grade_id) 
    SELECT 'foo bar', i.id, g.grade_id
    FROM (VALUES (0), (2), (3)) g(grade_id) CROSS JOIN
         i

edited Aug 11, 2016 at 2:10

answered Aug 10, 2016 at 22:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RandomNumberFun Over a year ago

This does not work if 'foo bar' has not been added yet. I want to be able to add both a new teacher and the grades at the same time.

Collectives™ on Stack Overflow

Insert into table, return id and then insert into another table with stored id

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related