时间戳

首页 > 解决方案 > 返回导致错误：缺少表的 FROM 子句条目

sql - 返回导致错误：缺少表的 FROM 子句条目

问题描述

我正在从 UUID 获取用户数据WHERE empl_user_pub_uuid = 'e2bb39f1f28011eab66c63cb4d9c7a34'。

因为我不想进行额外的查询来获取额外的用户数据，所以我试图通过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

我究竟做错了什么？

标签： sqlpostgresqlsql-insertgreatest-n-per-groupsql-returning

解决方案

根据 Postgres Docs 大约6.4。从修改的行返回数据：

在 INSERT 中，可用于 RETURNING 的数据是插入时的行。

但是在这里，您尝试从源表而不是目标返回列。返回将无法从表中返回列，_u而只能从employees.password_resets表的插入行返回。但是您可以编写嵌套 cte 进行插入，也可以从源表中选择数据。请尝试以下方法。

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'
), I as

(
    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
)
select _u.empl_user_pvt_uuid, _u.email, _u.name_first from _u

推荐阅读