How can I insert multiple rows using a value returned from the first row?

Question

I have a table, chat_rooms that has the following schema:

CREATE TABLE chat_rooms (
    id integer GENERATED BY DEFAULT AS IDENTITY,
    user_id uuid REFERENCES users(user_id) ON DELETE CASCADE,
    CONSTRAINT chat_rooms_pkey PRIMARY KEY (id, user_id)
);

When a chat_room row is created, I want to insert rows for all the users inside chat_room, but I need the first generated id to use in the following inserts. Any ideas?

This can be a sql or plpgsql function as well as a query.

Answer 1

You design doesn't work. It makes no sense to have id as IDENTITY column, combined with a PK on (id, user_id).

Proper design

CREATE TABLE chatroom (
  chatroom_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE chatroom_user (
  chatroom_id int  REFERENCES chatroom ON DELETE CASCADE
, user_id     int -- REFERENCES users ON DELETE CASCADE
, CONSTRAINT chatroom_user_pkey PRIMARY KEY (chatroom_id, user_id)
);

Then to insert an input array of existing users into the same, new chat room, use a data-modifying CTE:

WITH ins_chatroom AS (
   INSERT INTO chatroom DEFAULT VALUES
   RETURNING chatroom_id
   )
INSERT INTO chatroom_user (chatroom_id, user_id)
SELECT chatroom_id, u
FROM   ins_chatroom, unnest('{1,2,3}'::int[]) u  -- input users as array
RETURNING *;  -- just to show result in fiddle

fiddle

See:

• Insert data in 3 tables at a time using Postgres

Minimalist design

If you want to avoid the additional table for chatrooms, you can make do with a SEQUENCE, and good old sequence manipulation functions:

CREATE SEQUENCE IF NOT EXISTS public.chat_user_chat_id_seq;

CREATE TABLE chat_user (
  chat_id int DEFAULT nextval('public.chat_user_chat_id_seq')  -- optional default
, user_id int -- REFERENCES users ON DELETE CASCADE
, CONSTRAINT chat_rooms_pkey PRIMARY KEY (chat_id, user_id)
);

ALTER SEQUENCE public.chat_user_chat_id_seq OWNED BY chat_user.chat_id;

Then to insert an input array of existing users into the same, new chat room, use these two commands in the same session (better in the same transaction):

SELECT nextval('public.chat_user_chat_id_seq');

INSERT INTO chat_user (chat_id, user_id)
SELECT currval('public.chat_user_chat_id_seq'), unnest('{1,2,3}'::int[]) u
RETURNING *;  -- just to show result in fiddle

fiddle

See:

• Is there a way I can use currval() without selecting it more than once?
• Drop sequence and cascade