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

由 发布于 postgresqlsqldatabasesubquerydblink
 收藏

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

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));

这是我收到的错误：

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

有任何想法吗？

最佳答案

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)  ...

但这是一种极其昂贵的工作方式。

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

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;

这可以解决原始语句中的许多问题。

  • 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