PostgreSQL复杂查询报错：缺少表FROM子句条目

错误原因 #

你遇到的错误是因为查询第5行直接引用了表users_postcoderegion_groups_postcoderegions，但该表并没有被包含在查询的FROM或JOIN子句中，PostgreSQL无法找到这个表的引用入口。

修复后的完整查询 #

SELECT u."id"
FROM "users" u
JOIN "users_rosters_schedules" urs ON u."id" = urs."user_id"
JOIN "users_postcoderegion_groups" upg ON urs."user_postcoderegion_group_id" = upg."id"
-- 新增关联表的JOIN，添加别名upgrp
JOIN "users_postcoderegion_groups_postcoderegions" upgrp ON upg."id" = upgrp."user_postcoderegion_group_id"
JOIN "postcode_regions" pr ON pr."id" = upgrp."postcode_region_id"
WHERE u."role" = 'contractor'
  AND u."contractor_status" = 'active'
  AND urs."day" = '2023-12-01'
  AND urs."active" = true
  AND (
    EXISTS (
      SELECT *
      FROM jsonb_array_elements(to_jsonb(urs.capacity -> 'products')) AS i(item)
      WHERE (item ->> 'product_category_id')::int IN (1, 2)
        AND (item ->> 'qty')::int > (
          SELECT COUNT(o.id)
          FROM orders o
          JOIN orders_products_categories opc ON o.id = opc.order_id
          WHERE o.contractor_user_id = urs."user_id"
            AND o.status != 'cancelled'
            AND o.scheduled_pickup_date = urs."day"
            AND opc.product_category_id = CAST(item ->> 'product_category_id' AS INTEGER)
        )
    )
    AND EXISTS (
      SELECT *
      FROM "users_postcoderegion_groups" upg_nested
      WHERE upg_nested."id" = urs."user_postcoderegion_group_id"
        AND EXISTS (
          SELECT *
          FROM "postcode_regions" pr_nested
          JOIN "users_postcoderegion_groups_postcoderegions" upgrp_nested ON pr_nested."id" = upgrp_nested."postcode_region_id"
          WHERE pr_nested."postcode_region_id" = 8123
            AND upgrp_nested."user_postcode_region_group_id" = upg_nested."id"
        )
    )
  );

关键修改说明 #

1. 添加缺失的关联表JOIN：在users_postcoderegion_groups和postcode_regions之间，新增对users_postcoderegion_groups_postcoderegions的JOIN，通过user_postcoderegion_group_id关联到users_postcoderegion_groups，并给表起别名upgrp方便引用。
2. 修正关联条件：将第5行的pr."id" = "users_postcoderegion_groups_postcoderegions"."postcode_region_id"改为pr."id" = upgrp."postcode_region_id"，使用新增的别名引用字段。
3. 冗余条件移除：原查询最后的AND u."id" = urs."user_id"已经在第一个JOIN条件中定义，属于冗余条件，已删除。
4. 子查询简化：内部子查询中CAST(i.item ->> 'product_category_id' AS INTEGER)简化为CAST(item ->> 'product_category_id' AS INTEGER)，因为i是表别名，item是列名，直接引用即可。

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