“对表的FROM子句条目的无效引用"在Postgres查询中 [英] "invalid reference to FROM-clause entry for table" in Postgres query

问题描述

我有以下查询:

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子句条目的无效引用

invalid reference to FROM-clause entry for table "appointment_intakes"

添加后错误开始发生:

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

和

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

因为我需要计算产品数量.

because I needed to calculate the number of products.

如何避免发生此错误?

推荐答案

解释错误

该错误消息的直接原因是，任何显式JOIN的绑定都比逗号(,)强大，而逗号(,)等效于CROSS JOIN，但是(

Explain error

The immediate cause for the error message is that any explicit JOIN binds stronger than a comma (,) which is otherwise equivalent to a CROSS JOIN, but (per documentation):

注意::当两个以上时，后一种等效并不完全成立 之所以出现表格，是因为JOIN的绑定比逗号更紧密.例如 FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition与 FROM T1, T2 INNER JOIN T3 ON condition ，因为condition可以 在第一种情况下，引用T1，在第二种情况下引用.

Note: This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.

在最后的矿山上大胆强调.这正是确切导致您出错的原因.您可以对其进行修复:

Bold emphasis at the end mine. This is exactly the cause of your error. You could fix it:

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

但这不是查询中的唯一问题.见下文.

But that's not the only problem in your query. See below.

有人可能会认为Postgres应该看到LATERAL仅与左侧的表格有关.但是，要想迅速变得聪明起来会给您带来麻烦.最好对此严格.

One might argue that Postgres should see that LATERAL only makes sense in connection with the table to the left. But trying to be to smart quickly gets you in trouble. Better be strict about this.

我添加了表别名，并怀疑是否有表限定的所有列名.在此期间，我简化了JSON引用并减少了一些噪音. 查询 仍然不正确 :

I added table aliases and table-qualified all column names as suspected. While being at it, I simplified the JSON references and trimmed some noise. The query is still incorrect:

"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"

如果这是正确的，并且基于更多的假设，则解决方案可能是在子查询中进行计数，例如:

If that's correct and based on some more assumptions, the solution could be to do the count in a subquery, like:

基于以上假设:

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联接组合而引起的各种问题.更多:

Since you only need the count, I converted your LATERAL join into a correlated subquery, thereby avoiding the various problems arising from multiple 1:n joins combined. More:

• 什么是和PostgreSQL中的子查询之间有什么区别?
• 两个SQL LEFT JOINS产生不正确的结果

• What is the difference between LATERAL and a subquery in PostgreSQL?
• Two SQL LEFT JOINS produce incorrect result

您 需要 以正确地转义标识符，使用准备好的语句并将值作为值传递.不要将值连接到查询字符串中.这是随机错误或 SQL注入攻击的邀请.

You need to escape identifiers properly, use a prepared statement and pass values as values. Don't concatenate values into the query string. That's an invitation for random errors or SQL injection attacks.

这是PHP的最新示例:

Here is a recent example for PHP:

• 我尝试实施UPSERT的问题

这篇关于“对表的FROM子句条目的无效引用"在Postgres查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！