选择 postgres 列中不存在的值（无上限）-解网

问：

给定一个 postgres 表 foo，其中包含一个有一些间隙的列。有没有办法在不循环或使用过大的 to from 或 against 的情况下获取所述列中不存在的值？external_id BIGINTgenerate_sequenceexcludejoinn

假设它有带有 external_ids 的行，我希望它返回顺序值，它应该返回 .下一个选择（假设没有更改）应该返回 etc。因此，应该返回间隙和持续上升的顺序 ID。我可以做这样的事情1, 3, 5, 752, 4, 6, 8, 910, 11, 12, 13, 14

WITH
  id_series AS (
    SELECT
      generate_series(1, 10) AS ids
  )
SELECT
  id_series.ids
FROM
  id_series
  left join known_ids ki on id_series.ids = kid.id
where
  ki.id is null
limit 5;

但是，如果序列不够大，无法找到未使用的 ID，则可能无法返回足够的未使用 ID，只要返回了 5 个 ID，就必须循环。

我的表当前包含介于 50,000 和 1,800,000,000 之间的 ID。有些间隙有几百万个 ID 宽，有些其他范围没有另外几百万个间隙（可能需要多次循环或使用非常大的序列）

有没有聪明有效的方法可以解决这个问题？

这就是我这样做的原因。该表包含来自外部数据源。必须使用单个 ID 查询此数据源。数据源会随时间推移公开其实体。因此，ID 10 亿可能在 ID 5 之前是“公共”的。所以我需要知道我还没有收到响应的外部 ID 并定期查询它们。正如我之前所说，这是我无法更改的，因为我无法控制外部数据源。这是我无法更改的实际业务需求。external_ids

PostgreSQL 间隙和孤岛

最有效/高效的解决方案是什么都不做，这不是问题。接受差距是正常的和预期的。还要考虑列名意味着您不控制该值。那么，为什么要制造价值呢？此外，如果您正在生成值，这仍然不是问题。从您当前拥有的最大值（1,800,000,000）开始，每秒生成 1,000,000 个值，然后您有 290,000 年的时间超过 bigint 的最大值。external_id

0赞 Pascal 9/14/2023

因为我需要知道差距并定期查询外部来源的差距，@Belayer这不是数据卫生问题，差距也不是“问题”，它们只是我问题领域的一部分

0赞 Frank Heikens 9/14/2023

问题到底是什么？我看到在 1、3、5 范围内没有数字 2 和 4，但我没有看到问题。数字 1.1 和 2.8 也不存在。“id”代表“标识符”，数字 1 与数字 3 做相同的工作，即使数字 2 不存在。

1赞 Bergi 9/14/2023

@PhilipKelley 在这种情况下，正如 Philip 所建议的那样，还要保留一个表，其中包含仍然缺失且要查询的 id（或 id 范围）。存储每个范围的上次查询时间、响应是什么以及下次应扫描的时间。当您收到响应时，请删除/拆分范围以删除您找到实体的范围。

答：

1赞 Bergi 9/14/2023 #1

无论如何，您的查询都必须扫描整个表。我不认为使用类似的东西会太低效

SELECT generate_series(
  (SELECT MIN(external_id) FROM known),
  (SELECT MAX(external_id) FROM known)
)
EXCEPT
SELECT external_id FROM known

当然，这不会返回低于或高于现有 ID 的 id，并且生成的 id 可能少于您需要的值，但您可以在应用程序逻辑中轻松处理它。或使用 .ngenerate_series(1, max(external_id)+n)

如果实际上不需要单个 ID，而是需要每个间隙的范围，请查看间隙和孤岛查询，例如在 SQL 中查找序列的间隙，而无需创建其他表。

如果您不需要一次所有间隙，而只需要下一个间隙，并且不超过值，则可以编写迭代算法，使用递归 CTE 找到一个又一个间隙（直到您有足够的值）。n

EXCEPT对我来说非常慢，但是，在已知表中找不到任何东西的左连接在有限制的情况下非常快（这很好，因为我无论如何都在批量查询并且不需要一次所有 id）。我猜是因为generate_series够懒了。只有当使用偏移量时，它才会变慢，但这可以通过通过generate_series调用的下限进行偏移来解决。IDK 为什么我的印象是这会很慢..感谢您实际给出:)答案

上一个：MSSQL 查询开始和结束日期，其中日期依次为下一行 [重复]

下一个：基于日期重叠转换表

选择 postgres 列中不存在的值（无上限）

Select values that are not present in a postgres column (without upper limit)

评论

评论