PostgreSQL UPDATE似乎没有更新某些行

Question

我正在尝试从另一个表更新一个表，但有几行根本没有更新，而其他一百万行工作正常。

我使用的语句如下：

UPDATE lotes_infos l
SET quali_ambiental = s.quali_ambiental
FROM sirgas_lotes_centroid s
WHERE l.sql = s.sql AND l.quali_ambiental IS NULL;

它说更新了 647 行，但我看不到变化。 我也尝试过没有is null子句，结果是一样的。

如果我进行连接，它似乎按预期工作，我使用的连接查询是这个：

SELECT sql, l.quali_ambiental, c.quali_ambiental FROM lotes_infos l
JOIN sirgas_lotes_centroid c
USING (sql)
WHERE l.quali_ambiental IS NULL;

它返回 787 行(有些都是空的，没关系(，这是连接结果的示例：

sql     | quali_ambiental | quali_ambiental
------------+-----------------+-----------------
1880040001 |                 | PA 10
1880040001 |                 | PA 10
0863690003 |                 | PA 4
0850840001 |                 | PA 4
3090500003 |                 | PA 4
1330090001 |                 | PA 10
1201410001 |                 | PA 9
0550620002 |                 | PA 6
0430790001 |                 | PA 1
1340180002 |                 | PA 9

我使用QGIS可视化结果，但找不到任何提示来解释为什么会发生这种情况。sirgas_lotes_centroid来自另一个表，几何是多边形的质心。我使用质心执行更快的空间连接，现在需要将信息放入包含原始多边形的表中。

sql列的类型是text，quali_ambiental对两者都是varchar(6)的。

如果使用以下查询直接更新一行，则工作正常：

UPDATE lotes_infos
SET quali_ambiental = 'PA 1'
WHERE sql LIKE '0040510001';

Answer 1

如果您没有看到看似合理的数据修改查询的结果，则要问的第一个问题是：

您是否提交了交易？

默认情况下，许多客户端使用自动提交，但有些客户端不使用。即使在标准客户端 psql 中，您也可以使用BEGIN(或语法变体(启动显式事务以禁用自动提交。然后，在使用COMMIT实际提交事务之前，结果对其他事务不可见。它可能会无限期挂起(这会产生其他问题(，或者通过稍后的交互回滚。

也就是说，你提到：some are both null, that's ok.您需要避免昂贵的空更新

，例如：

UPDATE lotes_infos l
SET    quali_ambiental = s.quali_ambiental
FROM   sirgas_lotes_centroid s
WHERE  l.sql = s.sql
AND    l.quali_ambiental IS NULL
AND    s.quali_ambiental IS NOT NULL;  --!

相关：

• 如何(或我可以(在多列上选择不同？

示例中的重复1880040001可以有两种解释。任一lotes_infos.sql都不会UNIQUE(即使使用l.quali_ambiental IS NULL过滤后也是如此(。或者sirgas_lotes_centroid.sql不是UNIQUE.或两者兼而有之。

如果只是lotes_infos.sql，您的查询应该仍然有效。但是sirgas_lotes_centroid.sql中的重复项使查询不确定(正如@jjanes还指出的那样(。lotes_infos中的目标行可以在sirgas_lotes_centroid中有多个候选项。由于缺乏定义，结果是武断的。如果其中一个有quali_ambiental IS NULL，它可以解释你观察到的东西。

我建议的查询从表面上解决了观察到的问题，因为它排除了源表中的 NULL 值。但是，如果同一sirgas_lotes_centroid.sql可以有多个非空的不同quali_ambiental，则查询将保持中断状态，因为结果是任意的。您必须定义要选取的源行并将其转换为 SQL。

下面是一个如何做到这一点的示例(章节"多个匹配..."(：

• 更新列的值

始终包括任何此类问题的确切表定义(CREATE TABLE语句(。这将节省大量浪费在投机上的时间。

旁白：为什么sql列的类型是text？像1880040001这样的价值观让我觉得integer或bigint。如果是这样，text是一个代价高昂的设计错误。