Postgres查询中的"invalid reference to FROM-clause entry for table"错误

Question

Postgres查询中的"invalid reference to FROM-clause entry for table"错误

sqlpostgresqlcorrelated-subqueryjsonblateral

6

我有以下查询：

query =
    "SELECT
      data #>> '{id}'          AS id,
      data #>> '{name}'        AS name,
      data #>> '{curator}'     AS curator,
      data #>  '{$isValid}'    AS \"$isValid\",
      data #>  '{customer}'    AS customer,
      data #>  '{$createdTS}'  AS \"$createdTS\",
      data #>  '{$updatedTS}'  AS \"$updatedTS\",
      data #>  '{$isComplete}' AS \"$isComplete\",
      (count(keys))::numeric as \"numProducts\",
      created_at
    FROM
      appointment_intakes,
      LATERAL jsonb_object_keys(data #> '{products}') keys
    INNER JOIN
      appointment_intake_users
    ON
      appointment_intake_users.appointment_intake_id = appointment_intakes.id
    #{where_clause}
    GROUP BY id"

以下代码出现了如下错误：

无效的表“appointment_intakes” FROM 子句引用项

在我添加以下代码后，错误开始出现：

LATERAL jsonb_object_keys(data #> '{products}') keys

并且

(count(keys))::numeric as \"numProducts\"

因为我需要计算产品的数量。

如何避免出现这种错误？

- dipole_moment

从您的查询中并不清楚您未限定的列名（如data或id）确切指的是哪个表。请使用表别名并进行澄清。请参考我的答案中的存根。同时声明您的Postgres版本和用于连接查询的编程语言。看起来像是SQL注入攻击的邀请……当然还有表定义——在psql中使用\d tbl命令可以获取到。我们需要查看约束，例如PK和NOT NULL等。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

错误解释

该错误消息的直接原因是任何显式的JOIN比逗号(,)更强，而逗号实际上等同于CROSS JOIN，但是（根据文档）:

注意:当出现两个以上的表时，这种后一种等价关系不完全成立，因为JOIN的优先级高于逗号。例如， FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition与 FROM T1, T2 INNER JOIN T3 ON condition不同，因为条件可以在第一种情况下引用T1，但在第二种情况下不能引用。

加粗是我的强调。
这就是你的错误原因。你可以进行修复：

FROM  appointment_intakes
<b>CROSS JOIN</b> LATERAL jsonb_object_keys(data #> '{products}') keys
INNER JOIN appointment_intake_users ON ...

但这不是唯一的问题。继续阅读。

有人可能会认为，Postgres应该看到LATERAL只与左侧的表相关联才有意义。但事实并非如此。

假设

我添加了表别名，并怀疑所有列名称都是有资格的。在进行操作时，我简化了JSON引用并修剪了一些无用信息。这个查询仍然不正确:

SELECT i.data ->> 'id'          AS id,
       i.data ->> 'name'        AS name,
       i.data ->> 'curator'     AS curator,
       i.data ->  '$isValid'    AS "$isValid",
       i.data ->  'customer'    AS customer,
       i.data ->  '$createdTS'  AS "$createdTS",
       i.data ->  '$updatedTS'  AS "$updatedTS",
       i.data ->  '$isComplete' AS "$isComplete",
       count(k.keys)::numeric   AS "numProducts",
       u.created_at
FROM   appointment_intakes i
     , jsonb_object_keys(i.data -> 'products') AS k(keys)
JOIN   appointment_intake_users u ON u.appointment_intake_id = i.id
#{where_clause}
GROUP  BY i.id

原始查询

基于上述内容和更多的假设，解决方案可以是在子查询中进行计数：

SELECT i.data ->> 'id'          AS id,
       i.data ->> 'name'        AS name,
       i.data ->> 'curator'     AS curator,
       i.data ->  '$isValid'    AS "$isValid",
       i.data ->  'customer'    AS customer,
       i.data ->  '$createdTS'  AS "$createdTS",
       i.data ->  '$updatedTS'  AS "$updatedTS",
       i.data ->  '$isComplete' AS "$isComplete",
       (SELECT count(*)::numeric
        FROM   jsonb_object_keys(i.data -> 'products')) AS "numProducts",
       min(u.created_at)        AS created_at
FROM   appointment_intakes i
JOIN   appointment_intake_users u ON u.appointment_intake_id = i.id
--     #{where_clause}
GROUP  BY i.id;

由于您只需要计数，我将您的LATERAL连接转换为相关子查询，从而避免了多个1：n连接组合引起的各种问题。更多信息：

您需要适当地转义标识符，使用预处理语句并将值作为值传递。不要将值连接到查询字符串中。这会导致随机错误或 SQL注入攻击。最近PHP的示例：

我尝试实现UPSERT时遇到问题