我正在尝试在 Phoenix 应用程序中运行迁移，但 Postgrex 返回以下错误：
null value in column "inserted_at" violates not-null constraint
产生该错误的查询是：
execute "INSERT INTO contract_groups
(SELECT c.id, fg.group_id FROM contracts c, folder_groups fg
WHERE c.folder_id = fg.folder_id)"
我尝试将查询更新为：
execute "INSERT INTO contract_groups
(contract_id, group_id, inserted_at)
VALUES ((SELECT c.id, fg.group_id FROM contracts c, folder_groups fg
WHERE c.folder_id = fg.folder_id), NOW())"
但我收到另一个错误说subquery must return only one column。
这是表的粗略定义。
insert into contract_groups (contract_id, group_id, inserted_at)
select c.id, fg.group_id, CURRENT_TIMESTAMP
from contracts c
inner join folder_groups fg
on fg.folder_id = c.folder_id
请注意，这取决于所选的列与语句中的列的顺序相匹配
更新：
根据评论，尝试：
insert into contract_groups (contract_id, group_id, inserted_at)
select distinct c.id, -- distinct added to prevent duplicates
fg.group_id,
CURRENT_TIMESTAMP
from contracts c
inner join folder_groups fg
on fg.folder_id = c.folder_id
where not exists ( -- exclude any combos that are in the target table
select 1
from contract_groups cg
where cg.contract_id = c.id
and fg.froup_id = cg.group_id
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句