CODEWITHSUNDEEP
postgresqlmany-to-manysql-insert
RandomNumberFun
RandomNumberFun

Reputation: 642

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

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).

Upvotes: 1

Views: 2341

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

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

Upvotes: 1

Related Questions