为多行插入查询，其中两列数据始终保持不变

Question

我有一个查询来插入多行，其中每行的两列数据保持不变。有没有什么方法可以编写优化的查询，而不是再次放置插入语句。

INSERT INTO public.shops(id, shop_id, tier_id)
VALUES
(uuid_generate_v1(),'8011',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),
(uuid_generate_v1(),'8012',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),
(uuid_generate_v1(),'8013',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),
(uuid_generate_v1(),'8014',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),
(uuid_generate_v1(),'8015',(SELECT ID FROM PUBLIC.BETTING_TIER WHERE NAME='Daily Million 6 - North Ire')),

Answer 1

以纯INSERT INTO ... SELECT:的形式重新表述

INSERT INTO public.shops (id, shop_id, tier_id)
SELECT uuid_generate_v1(), t2.shop_id, t1.ID
FROM PUBLIC.BETTING_TIER t1
CROSS JOIN
(SELECT '8011' AS shop_id UNION ALL SELECT '8012' UNION ALL SELECT '8013'
UNION ALL '8014' UNION ALL '8015') t2
WHERE t1.NAME = 'Daily Million 6 - North Ire';

Answer 2

Tim的解决方案的一个替代方案是使用generate_series创建商店ID，以防它们是连续的：

INSERT INTO public.shops(id, shop_id, tier_id)
SELECT uuid_generate_v1(), i::text, b.id 
FROM generate_series(8011,8015,1) i
CROSS JOIN betting_tier b
WHERE b.name = 'Daily Million 6 - North Ire';