How to specify native table fields and a foreign table field during an insert?

Suppose the following:

create table member (
    id serial primary key,
    member_name varchar(55),
    role_ids bigint[] not null
);

create table role (
    id serial primary key,
    role_name varchar(55) unique
);

insert into role values (1, 'admin'), (2, 'common');

I can create an admin member like this:

insert into member (role_ids)
select ARRAY[id] as role_id from role where role_name = 'admin';

But how can I specify other fields, like member_name as well?

I’ve tried this:

insert into member (member_name, role_ids) values('test member', role_ids)
select ARRAY[id::bigint] as role_id from role where role_name = 'admin';

But this throws an error, error at or near select

>Solution :

In your case I would probably choose to use a nested SELECT inside the VALUES, to emphasize that this is a lookup that you expect to succeed and return only one value:

insert into member (member_name, role_ids)
values('test member',
    (select ARRAY[id] from role where role_name = 'admin'));

This wouldn’t work if you were selecting more than one column in your select. Another solution would be to just use SELECT and no VALUES, because nothing stops you from returning literal values in your SELECT. You don’t name the columns in the select for your insert, instead you order them to match the order of the columns in the insert:

insert into member (member_name, role_ids)
select 'test member', ARRAY[id] from role where role_name = 'admin';