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.

user330315 · Accepted Answer · 2018-02-21 18:12:22Z

15

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.

answered Feb 21, 2018 at 18:12

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Rabhi salim Over a year ago

Do you know how to create a new select query inside the values of the cross join?

Laurenz Albe · Accepted Answer · 2018-02-21 18:09:39Z

3

To insert multiple rows, just omit VALUES:

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

answered Feb 21, 2018 at 18:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Postgresql: Inserting values for each row returned by a select

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related