Postgres嵌套WITH查询报错：缺失new_user表FROM子句条目

我本可以通过事务和多个查询解决问题，但为了学习新内容，尝试将所有操作压缩为一个请求。现有表结构：deftables（存储所有表名）、tableheader（存储记录组标题）、acl（存储权限等级）、user2acl（关联用户、表及记录组）、personnes（用户数据）、pattributs（用户额外数据）。

测试SQL如下：

WITH acl_asker_max AS
(   
  SELECT max(aclnames_asker.value) AS max_value FROM personnes AS asker_user
  JOIN deftables AS acl_names ON acl_names.name = 'newagenda'
  JOIN tableheader AS acl_index ON acl_index.name = 'sometype'
  JOIN user2acl AS acl_asker ON asker_user.id = acl_asker.userid
  JOIN acl AS aclnames_asker ON acl_asker.aclid = aclnames_asker.id
        WHERE asker_user.id = 5 AND acl_asker.tableid = acl_index.id
        AND acl_index.id is not null ), 
  check_acl AS (
 SELECT CASE WHEN EXISTS
     ( SELECT 1 FROM acl_asker_max, acl
   WHERE acl.name = 'caninsert'
      AND acl.value <= (SELECT max_value FROM acl_asker_max) )
     THEN true
           ELSE false END ),
   new_user AS (
 INSERT INTO personnes (login, passwd)
    SELECT DISTINCT 'titi' as login, 'something' as passwd FROM personnes
       WHERE NOT EXISTS (SELECT 1 FROM personnes WHERE login = 'titi')
    AND (SELECT true FROM check_acl)
 ON CONFLICT (login) 
               DO UPDATE SET passwd = excluded.passwd
 RETURNING id)      
 INSERT INTO pattributs (persid, name, value)
       SELECT new_user.id, name, value FROM (VALUES ('referentid', '3'),
         ('name', 'PasseP'),
   ('prenom', 'Titi')) AS data(name, value)
 JOIN new_user ON true
 ON CONFLICT (persid, name) WHERE persid = new_user.id
   DO UPDATE SET value = excluded.value;

执行后报错：

ERROR:  missing FROM-clause entry for table "new_user"

报错行：LINE 30: ON CONFLICT (persid, name) WHERE persid = new_user.id

我已在第17行定义了带RETURNING id的new_user CTE，按理解该表应存在且包含id列，疑惑是JOIN new_user ON true导致的问题吗？想确认new_user为何无法被识别。

问题原因 #

PostgreSQL的ON CONFLICT子句的WHERE过滤条件只能引用目标表（pattributs）的列或者excluded行（即准备插入的行）的列，无法直接引用SELECT语句中关联的CTE（比如这里的new_user）或其他表。这是因为ON CONFLICT的上下文只局限于目标表和待插入的行，外层查询的表别名在这里不可见。

修改方法 #

把WHERE persid = new_user.id替换为WHERE persid = excluded.persid——因为excluded.persid就是你从new_user获取的id值，和new_user.id完全一致，这样就能正确限定只更新当前用户的属性行。

修改后的完整SQL：

WITH acl_asker_max AS
(   
  SELECT max(aclnames_asker.value) AS max_value FROM personnes AS asker_user
  JOIN deftables AS acl_names ON acl_names.name = 'newagenda'
  JOIN tableheader AS acl_index ON acl_index.name = 'sometype'
  JOIN user2acl AS acl_asker ON asker_user.id = acl_asker.userid
  JOIN acl AS aclnames_asker ON acl_asker.aclid = aclnames_asker.id
        WHERE asker_user.id = 5 AND acl_asker.tableid = acl_index.id
        AND acl_index.id is not null ), 
  check_acl AS (
 SELECT CASE WHEN EXISTS
     ( SELECT 1 FROM acl_asker_max, acl
   WHERE acl.name = 'caninsert'
      AND acl.value <= (SELECT max_value FROM acl_asker_max) )
     THEN true
           ELSE false END ),
   new_user AS (
 INSERT INTO personnes (login, passwd)
    SELECT DISTINCT 'titi' as login, 'something' as passwd FROM personnes
       WHERE NOT EXISTS (SELECT 1 FROM personnes WHERE login = 'titi')
    AND (SELECT true FROM check_acl)
 ON CONFLICT (login) 
               DO UPDATE SET passwd = excluded.passwd
 RETURNING id)      
 INSERT INTO pattributs (persid, name, value)
       SELECT new_user.id, name, value FROM (VALUES ('referentid', '3'),
         ('name', 'PasseP'),
   ('prenom', 'Titi')) AS data(name, value)
 JOIN new_user ON true
 ON CONFLICT (persid, name) WHERE persid = excluded.persid
   DO UPDATE SET value = excluded.value;

补充说明 #

如果你的(persid, name)已经是pattributs表的唯一约束/主键，那么其实可以省略WHERE子句——因为冲突只会发生在你当前插入的这组(persid, name)上，不过保留WHERE persid = excluded.persid能更明确地限定作用范围，避免意外修改其他行。

内容的提问来源于stack exchange，提问作者Noh Kumado