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
– holyghostgym, Commented Mar 17, 2019 at 23:35
Where will the userid to insert into the new groupmembers record come from? — 404
– 404, Commented Mar 17, 2019 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
– 404, Commented Mar 18, 2019 at 0:24
@404: Did you see FOREIGN KEY (creator) references users (email) on delete CASCADE? That's how. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 18, 2019 at 1:44

Erwin Brandstetter · Accepted Answer · 2019-03-18 01:38:38Z

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

Collectives™ on Stack Overflow

How to achieve nested INSERT statement in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related