How to achieve nested INSERT statement in PostgreSQL?

Question

I have two tables, group and groupmembers. On insertion of a row in the group table, I also want to insert two values, groupid (id from group table) and userid (id of the user that created the group) into the groupmembers table. These are the tables:

CREATE TABLE groups (
  id SERIAL PRIMARY KEY NOT NULL,
  name CHARACTER VARYING(255) NOT NULL,
  creator CHARACTER VARYING(255) NOT NULL,
  role CHARACTER VARYING(100) NOT NULL DEFAULT ('admin'),
  createdon TIMESTAMP WITH TIME ZONE DEFAULT now(),
  FOREIGN KEY (creator) references users (email) on delete CASCADE
);

CREATE TABLE groupmembers (
  id SERIAL PRIMARY KEY NOT NULL,
  groupid INTEGER NOT NULL,
  userid INTEGER NOT NULL,
  createdon TIMESTAMP WITH TIME ZONE DEFAULT now(),
  FOREIGN KEY (groupid) references groups (id) on delete CASCADE,
  FOREIGN KEY (userid) references users (id) on delete CASCADE
);

 CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    firstname CHARACTER VARYING(255) NOT NULL,
    lastname CHARACTER VARYING(255) NOT NULL,
    email CHARACTER VARYING(50) UNIQUE NOT NULL,
    password CHARACTER VARYING(255) NOT NULL,
    registeredon TIMESTAMP WITH TIME ZONE DEFAULT now()
  );

The insert statement into the group table is:

INSERT INTO groups (name, creator) VALUES ($1, $2) RETURNING *;

How do I add another insert statement that inserts values into groupid and userid columns of groupmembers table?

I have seen this, but it doesn't seem to answer my question:
PostgreSQL nested INSERTs / WITHs for foreign key insertions

@KenWhite; thanks for the good spot. my caps lock was actually on. Thanks — holyghostgym, Mar 17 '19 at 23:35
Where will the `userid` to insert into the new `groupmembers` record come from? — 404, Mar 17 '19 at 23:59
How? You showed an insertion into `groups`, and presumably `id` from the inserted record will be `groupid` in `groupmembers`, but then how do you know which user id to use? — 404, Mar 18 '19 at 00:24
@404: Did you see `FOREIGN KEY (creator) references users (email) on delete CASCADE`? That's how. — Erwin Brandstetter, Mar 18 '19 at 01:44

Erwin Brandstetter · Answer 1 · 2019-03-18T01:38:38.633

I suggest to wrap both inserts in a single query with a data-modifying CTE, like:

WITH grp_ins AS (
   INSERT INTO groups (name, creator)
   VALUES ($1, $2)
   RETURNING id, creator
   )
INSERT INTO groupmembers (groupid, userid)
SELECT g.id, u.id 
FROM   grp_ins    g
JOIN   users u ON u.email = g.creator;

Since groups.creator is defined NOT NULL with an FK constraint enforcing referential integrity, an [INNER] JOIN is good. Else I'd consider a LEFT JOIN.

Much like the answer you referred to. Or these ones:

If, for some reason, you cannot enforce above command (like nested in a function that has to be used for inserts to groups), the next best thing is a trigger AFTER INSERT to execute the second INSERT. A bit more complex and costly. But does the job:

CREATE OR REPLACE FUNCTION trg_ins_row_in_groupmembers()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO groupmembers (groupid, userid)
   SELECT NEW.id, (SELECT u.id FROM users u WHERE u.email = NEW.creator);

   RETURN NEW;  -- doesn't matter much what you return here
END
$func$  LANGUAGE plpgsql;

And a trigger AFTER INSERT on groups:

CREATE TRIGGER groups_ins_aft
AFTER INSERT ON groups
FOR EACH ROW EXECUTE PROCEDURE trg_ins_row_in_groupmembers();

On INSERT to a table INSERT data in connected tables

How to achieve nested INSERT statement in PostgreSQL?

1 Answers1

Related