公用表表达式，PostgreSQL中的WITH子句；错误：关系“ stkpos”不存在 [英] Common table expression,WITH clause in PostgreSQL ;ERROR: relation "stkpos" does not exist

问题描述

例如以下是我的查询

WITH stkpos as (
select * from mytbl
),
updt as (
update stkpos set field=(select sum(fieldn) from stkpos)
)
select * from stkpos

错误：关系 stkpos不存在

ERROR: relation "stkpos" does not exist

推荐答案

与MS-SQL和其他一些DB不同，PostgreSQL的CTE术语不被视为视图。它们更像是隐式的临时表-它们得到了实现，计划者无法将过滤器向下推入其中或将过滤器拉出其中，等等。

Unlike MS-SQL and some other DBs, PostgreSQL's CTE terms are not treated like a view. They're more like an implicit temp table - they get materialized, the planner can't push filters down into them or pull filters up out of them, etc.

一个这样的结果是您无法更新它们，因为它们是原始数据的副本，而不仅仅是它们的视图。您需要找到另一种方式来做自己想要的事情。

One consequence of this is that you can't update them, because they're a copy of the original data, not just a view of it. You'll need to find another way to do what you want.

如果您需要帮助，则需要发布一个清晰且自我的新问题。包含的示例（带有 create table 语句等）显示了您实际问题的简化版本。实际上足以让我们了解您要完成的工作以及原因。

If you want help with that you'll need to post a new question that has a clear and self contained example (with create table statements, etc) showing a cut down version of your real problem. Enough to actually let us understand what you're trying to accomplish and why.

这篇关于公用表表达式，PostgreSQL中的WITH子句；错误：关系“ stkpos”不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！