插入数据时如何同时指定本地表字段与关联表字段？

问题背景 #

现有如下表结构与初始数据：

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');

已知可以通过以下语句创建拥有admin角色的member：

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

但尝试同时指定member_name字段时执行报错：

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';

错误提示：error at or near select

错误原因 #

你混用了INSERT ... VALUES和INSERT ... SELECT两种语法，这两种插入方式不能混合使用。VALUES用于插入固定值，SELECT用于插入查询结果，二者需单独使用。

正确写法 #

写法一：SELECT语句中包含固定字段值 #

将member_name的固定值直接放入SELECT列表，与查询得到的role_ids一同返回插入：

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

这种方式最通用，无论查询返回单行还是多行都能正常插入（多行时会创建多个member，每个都使用指定的member_name）。

写法二：VALUES中使用子查询获取role_ids #

若偏好VALUES语法，可将查询role_ids的逻辑写成子查询（需确保子查询仅返回单行结果）：

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

注意：如果子查询返回多行，这种写法会报错，此时建议使用写法一。

内容的提问来源于stack exchange，提问作者Mike K