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

问题描述

我有两个单独的数据库。我正在尝试将一个数据库中的列更新为另一数据库中的列的值：

 更新客户
 SET customer_id = 
（SELECT t1 FROM dblink（'port = 5432，dbname = SERVER1 user = postgres password = 309245'，
'SELECT store_key FROM store'）AS（t1整数））; 
  

这是我收到的错误：

< blockquote>

 错误：由子查询返回的多行用作表达式
  

有什么想法吗？

解决方案

从技术上说 ，要修复您的陈述，您可以在子查询中添加 LIMIT 1 以确保返回最多1行。

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

实际上 ，您想要匹配行 而不是从远程表 store 中选择任意行以更新本地表的每一行客户。

您的基本问题没有提供足够的详细信息，所以我 假设 一个文本列<$ c为方便起见，在两个表中都使用$ c> match_name （在 store 中使用 UNIQUE ）此示例：

  ...'从存储区
中选择SELECT store_key  WHERE match_name ='|| quote_literal（customer.match_name） ...  

但这是一种极其昂贵的处理方式。

理想 ，您应该完全重写该语句。

  UPDATE客户c 
 SET customer_id = s.store_key 
 FROM dblink（'port = 5432，dbname = SERVER1用户= postgres密码= 309245'
，'SELECT match_name，store_key FROM store'）
 AS s（match_name文本，store_key整数）
 W.C.match_name = s.match_name 
和c.customer_id与s.store_key是不同的； 
  

这可以纠正原始语句中的许多问题。

• 很显然，导致出现错误的基本问题是固定的。




• 在 <$ c $ UPDATE 语句的c> FROM 子句，而不是对每一行运行相关子查询。




• 使用dblink时，以上内容变得重要一千倍。您不想为每一行都调用 dblink（），这非常昂贵。调用一次即可检索所需的所有行。




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




• 通常，当实际没有任何更改时，您不想更新行。不采取任何措施代价昂贵（但仍然会产生死行）。 WHERE 子句中的最后一个表达式可防止此类空更新：

   和c.customer_id与sub.store_key 
    

的距离是

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 should 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 almost always 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 form, 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
  

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