Postgresql: Inserting values for each row returned by a select

Question

Hi I'm having trouble trying to figure out how to build the following query:

INSERT INTO role_permissions (role_id, permission)
VALUES (
   (SELECT role_id
    FROM role_permissions
    WHERE permission = 'manage_admins'),
   'create_admins');

INSERT INTO role_permissions (role_id, permission)
VALUES (
   (SELECT role_id
    FROM role_permissions
    WHERE permission = 'manage_admins'),
   'edit_admins');

So basically some context, there is a permission called manage_admins that would allow users to edit/create, but now I need to separate this permission into 2 different permissions. The thing is I also have a role_permissions table where I store all of a role's permissions.

So I need to insert the 2 new permissions for every role that had the old permission, the example I provided is failing because the SELECT query is returning multiple values. So this is what I'm having trouble with, so any help would be greatly appreciated.

Answer 1

Get rid of values:

INSERT INTO role_permissions (role_id, permission)
SELECT rp.role_id, t.permission
FROM role_permissions rp
  cross join (values ('edit_admins'), ('create_admins')) as t(permission)
WHERE rp.permission = 'manage_admins';

The cross join will create two rows with the same role_id and the select will then insert those IDs together with the new permission name into the table.

Answer 2

To insert multiple rows, just omit VALUES:

INSERT INTO role_permissions (...)
SELECT ...