返回导致错误:缺少表的FROM子句条目 [英] RETURNING causes error: missing FROM-clause entry for table

问题描述

我从UUID获取用户数据 WHERE empl_user_pub_uuid ='e2bb39f1f28011eab66c63cb4d9c7a34'.

I am getting the users data from UUID WHERE empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'.

由于我不想进行其他查询来获取其他用户数据，因此我试图通过 INSERT 对其进行潜行.

Since I don't want to make an additional query to fetch additional user data I'm trying to sneak them through the INSERT.

WITH _u AS (
    SELECT
        eu.empl_user_pvt_uuid,
        ee.email,
        ep.name_first
    FROM employees.users eu
    LEFT JOIN (
        SELECT DISTINCT ON (ee.empl_user_pvt_uuid)
            ee.empl_user_pvt_uuid,
            ee.email
        FROM employees.emails ee
        ORDER BY ee.empl_user_pvt_uuid, ee.t DESC
    ) ee ON eu.empl_user_pvt_uuid = ee.empl_user_pvt_uuid
    LEFT JOIN (
        SELECT DISTINCT ON (ep.empl_user_pvt_uuid)
            ep.empl_user_pvt_uuid,
            ep.name_first
        FROM employees.profiles ep
    ) ep ON eu.empl_user_pvt_uuid = ep.empl_user_pvt_uuid
    WHERE empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'
)
INSERT INTO employees.password_resets (empl_pwd_reset_uuid, empl_user_pvt_uuid, t_valid, for_empl_user_pvt_uuid, token)
SELECT 'f70a0346-a077-11eb-bd1a-aaaaaaaaaaaa', '6efc2b7a-f27e-11ea-b66c-de1c405de048', '2021-04-18 19:57:47.111365', _u.empl_user_pvt_uuid, '19d65aea-7c4a-41bc-b580-9d047f1503e6'
FROM _u
RETURNING _u.empl_user_pvt_uuid, _u.email, _u.name_first;

但是我得到了

[42P01] ERROR: missing FROM-clause entry for table "_u" 
Position: 994

我在做什么错了?

推荐答案

确实，如前所述， INSERT 的 RETURNING 子句只能看到插入的排.更具体地说，在此处引用该手册:

It's true, as has been noted, that the RETURNING clause of an INSERT only sees the inserted row. More specifically, quoting the manual here:

可选的 RETURNING 子句使 INSERT 进行计算并返回基于实际插入的每一行的值(或更新，如果使用了 ON CONFLICT DO UPDATE 子句).这主要用于获取默认值提供的值，例如序列号序列号.但是，任何使用表列的表达式. RETURNING 列表的语法与之相同. SELECT 的输出列表.仅成功的行插入或更新的内容将被返回.[...]

The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned. [...]

Bold 重点.
因此，没有什么可以阻止您向 RETURNING 列表中添加相关子查询:

INSERT INTO employees.password_resets AS ep
       (empl_pwd_reset_uuid                  , empl_user_pvt_uuid                    , t_valid                     , for_empl_user_pvt_uuid, token)
SELECT 'f70a0346-a077-11eb-bd1a-aaaaaaaaaaaa', '6efc2b7a-f27e-11ea-b66c-de1c405de048', '2021-04-18 19:57:47.111365', eu.empl_user_pvt_uuid , '19d65aea-7c4a-41bc-b580-9d047f1503e6'
FROM   employees.users eu
WHERE  empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'
RETURNING for_empl_user_pvt_uuid AS empl_user_pvt_uuid  -- alias to meet your org. query
        , (SELECT email
           FROM   employees.emails
           WHERE  empl_user_pvt_uuid = ep.empl_user_pvt_uuid
           ORDER  BY t DESC  -- NULLS LAST ?
           LIMIT  1
          ) AS email
        , (SELECT name_first
           FROM   employees.profiles
           WHERE  empl_user_pvt_uuid = ep.empl_user_pvt_uuid
           -- ORDER  BY ???
           LIMIT  1
          ) AS name_first;

与您出于多种原因提出的查询(或提出的查询)相比，这也效率更高.

This is also much more efficient than the query you had (or what was proposed) for multiple reasons.

• 我们不在表 employees.emails 和的所有行上运行子查询 ee 和 ep employee.profiles .如果我们需要这些表的主要部分，那将是有效的，但我们仅从每个表中获取一行感兴趣的行.使用适当的索引，相关子查询的效率要高得多.参见:

• We don't run the subqueries ee and ep over all rows of the tables employees.emails and employees.profiles. That would be efficient if we needed major parts of those tables, but we only fetch a single row of interest from each. With appropriate indexes, a correlated subquery is much more efficient for this. See:

• 高效的查询，可以从大表中获取每个组的最大价值
• 两个SQL LEFT JOINS产生不正确的结果
• 在每个GROUP BY组中选择第一行?
• 将GROUP BY查询优化为检索每个用户的最新行

我们不增加一个或多个CTE的开销.

We don't add the overhead of one or more CTEs.

我们仅在成功插入 INSERT 后 获取其他数据，因此，如果插入由于任何原因而没有通过，则不会浪费时间.(请参阅顶部的引用！)

We only fetch additional data after a successful INSERT, so no time is wasted if the insert didn't go through for any reason. (See quote at the top!)

可能最重要的是，这是正确.我们使用实际插入的行中的数据-插入后 .(请参见顶部的引用！)在可能的默认值之后，将应用触发器或规则.可以肯定的是，我们所看到的是数据库中(当前)的实际内容.

Plus, possibly most important, this is correct. We use data from the row that has actually been inserted - after inserting it. (See quote at the top!) After possible default values, triggers or rules have been applied. We can be certain that what we see is what's actually in the database (currently).

您没有 profiles.name_first 的 ORDER BY .那是不对的.要么只有一行合格行，那么我们就不需要 DISTINCT 或 LIMIT 1 .或者可以有多个，那么我们还需要确定性的 ORDER BY 以获得确定性的结果.

You have no ORDER BY for profiles.name_first. That's not right. Either there is only one qualifying row, then we need no DISTINCT nor LIMIT 1. Or there can be multiple, then we also need a deterministic ORDER BY to get a deterministic result.

如果 emails.t 可以为NULL，则需要在 ORDER BY 子句中添加 NULLS LAST .参见:

And if emails.t can be NULL, you'll want to add NULLS LAST in the ORDER BY clause. See:

• 按列ASC排序，但NULL值优先?

理想情况下，您具有以下多列索引(按此顺序排列的列):

Ideally, you have these multicolumn indexes (with columns in this order):

• 用户(empl_user_pub_uuid，empl_user_pvt_uuid)
• 电子邮件(empl_user_pvt_uuid，电子邮件)
• 配置文件(empl_user_pvt_uuid，名字为第一名)

然后，如果对表进行了足够的清理，则将获得三个仅索引的扫描，并且整个操作将迅速减轻.

Then, if the tables are vacuumed enough, you get three index-only scans and the whole operation is lightening fast.

如果您真的想要(我认为您不想要)，请考虑:

If you really want that (which I don't think you do), consider:

• 仅使用SQL返回更新前的列值

这篇关于返回导致错误:缺少表的FROM子句条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！