如何为无关联bar的foo创建并关联bar？PostgreSQL技术问题

错误原因 #

• 「DEFAULT is not allowed in this context」：你应该是在INSERT INTO foo_bar时直接把DEFAULT作为bar_id的值，但bar_id必须关联已存在的bar记录主键，不能直接用DEFAULT——DEFAULT是针对bar表的id字段的默认值逻辑，不适用于foo_bar的外键字段。
• 「missing FROM-clause entry for table 'f'」：你在SQL里引用了表别名f，但没有在FROM子句里声明这个别名，或者别名拼写有误，导致数据库找不到对应的表。

正确实现SQL #

方法1：分步关联（逻辑清晰，推荐） #

用WITH子句先找出未关联的foo，再为它们创建bar记录，最后建立关联：

WITH unlinked_foo AS (
    -- 筛选所有没有关联bar的foo
    SELECT id AS foo_id
    FROM foo
    LEFT JOIN foo_bar fb ON foo.id = fb.foo_id
    WHERE fb.foo_id IS NULL
), new_bars AS (
    -- 为每个未关联的foo插入一条bar记录，同时返回对应的foo_id和生成的bar_id
    INSERT INTO bar DEFAULT VALUES
    SELECT foo_id, currval('bar_id_seq')
    FROM unlinked_foo
    RETURNING foo_id, id AS bar_id
)
-- 将新生成的bar与foo关联
INSERT INTO foo_bar (foo_id, bar_id)
SELECT foo_id, bar_id
FROM new_bars;

注：bar_id_seq是bar表id字段（serial类型）自动生成的序列名，若不确定可以用SELECT pg_get_serial_sequence('bar', 'id');查询实际序列名。

方法2：单语句快速实现 #

如果想用一条语句完成，可以先批量插入bar记录，再关联到对应的foo：

-- 先为所有未关联的foo插入bar记录
INSERT INTO bar DEFAULT VALUES
SELECT 1
FROM foo
WHERE id NOT IN (SELECT foo_id FROM foo_bar);

-- 建立foo和新bar的关联
INSERT INTO foo_bar (foo_id, bar_id)
SELECT f.id, b.id
FROM foo f
JOIN (
    -- 取刚插入的bar记录（按id倒序取对应数量）
    SELECT id, row_number() OVER (ORDER BY id) AS rn
    FROM bar
    WHERE id > (SELECT COALESCE(MAX(id), 0) FROM bar WHERE id IN (SELECT bar_id FROM foo_bar))
) b ON row_number() OVER (ORDER BY f.id) = b.rn
WHERE f.id NOT IN (SELECT foo_id FROM foo_bar);

性能优化提示 #

如果foo表数据量较大，NOT IN的查询效率可能不高，推荐用LEFT JOIN替代（如方法1中的写法），避免子查询的性能损耗。

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