Postgres 错误:用作表达式的子查询返回多行 [英] Postgres Error: More than one row returned by a subquery used as an expression

问题描述

我有两个独立的数据库.我正在尝试将一个数据库中的列更新为另一个数据库中的列的值:

更新客户设置客户 ID=(SELECT t1 FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245','SELECT store_key FROM store') AS (t1 integer));

这是我收到的错误:

<块引用>

ERROR: 子查询返回多行用作表达式

有什么想法吗?

解决方案

技术上，要修复你的语句，你可以添加LIMIT 1 到子查询以确保最多返回 1 行.那将消除错误，您的代码仍然是胡说八道.

... 'SELECT store_key FROM store LIMIT 1' ...

实际上，您希望以某种方式匹配行，而不是从远程表store中选择任意行更新本地表 customer 的每一行.
您的基本问题没有提供足够的细节，所以我假设两个表中的文本列 match_name(和 UNIQUEstore 中的 code>):

...'SELECT store_key FROM storeWHERE match_name = ' ||quote_literal(customer.match_name)  ...

但这是一种极其昂贵的做事方式.

理想情况下，你完全重写了语句.

更新客户 cSET customer_id = s.store_keyFROM dblink('port=5432, dbname=SERVER1 user=postgres 密码=309245', 'SELECT match_name, store_key FROM store')AS s(match_name text, store_key integer)WHERE c.match_name = s.match_name并且 c.customer_id 与 s.store_key 不同；

这解决了您原始陈述中的一些问题.

显然，导致您的错误的基本问题已修复.

通常最好在 FROMUPDATE 语句 的code> 子句，而不是为每一行运行相关子查询.

使用 dblink 时，上述内容变得重要一千倍.您不想为每一行调用 dblink()，这非常昂贵.调用一次即可检索您需要的所有行.

对于相关子查询，如果在子查询中未找到任何行，则该列将更新为 NULL，这几乎总是不是您想要的.在我更新的查询中，只有找到匹配的行才会更新该行.否则，该行不被触及.

通常情况下，您不会想要更新行，但实际上并没有发生任何变化.这是代价高昂的无所作为(但仍会产生死行).WHERE 子句中的最后一个表达式可防止此类空更新:

 AND c.customer_id 与 sub.store_key 不同

相关:

• 我该怎么做(或者我可以)在多列上选择 DISTINCT 吗?

I have two separate databases. I am trying to update a column in one database to the values of a column from the other database:

UPDATE customer
SET customer_id=
   (SELECT t1 FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245',
   'SELECT store_key FROM store') AS (t1 integer));

This is the error I am receiving:

ERROR:  more than one row returned by a subquery used as an expression

Any ideas?

解决方案

Technically, to repair your statement, you can add LIMIT 1 to the subquery to ensure that at most 1 row is returned. That would remove the error, your code would still be nonsense.

... 'SELECT store_key FROM store LIMIT 1' ...

Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table store to update every row of your local table customer.
Your rudimentary question doesn't provide enough details, so I am assuming a text column match_name in both tables (and UNIQUE in store) for the sake of this example:

... 'SELECT store_key FROM store
     WHERE match_name = ' || quote_literal(customer.match_name)  ...

But that's an extremely expensive way of doing things.

Ideally, you completely rewrite the statement.

UPDATE customer c
SET    customer_id = s.store_key
FROM   dblink('port=5432, dbname=SERVER1 user=postgres password=309245'
            , 'SELECT match_name, store_key FROM store')
       AS s(match_name text, store_key integer)
WHERE c.match_name = s.match_name
AND   c.customer_id IS DISTINCT FROM s.store_key;

This remedies a number of problems in your original statement.

Obviously, the basic problem leading to your error is fixed.

It's typically better to join in additional relations in the FROM clause of an UPDATE statement than to run correlated subqueries for every individual row.

When using dblink, the above becomes a thousand times more important. You do not want to call dblink() for every single row, that's extremely expensive. Call it once to retrieve all rows you need.

With correlated subqueries, if no row is found in the subquery, the column gets updated to NULL, which is almost always not what you want. In my updated query, the row only gets updated if a matching row is found. Else, the row is not touched.

Normally, you wouldn't want to update rows, when nothing actually changes. That's expensively doing nothing (but still produces dead rows). The last expression in the WHERE clause prevents such empty updates:

     AND   c.customer_id IS DISTINCT FROM sub.store_key

Related:

• How do I (or can I) SELECT DISTINCT on multiple columns?

这篇关于Postgres 错误:用作表达式的子查询返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！