PostgreSQL 中针对范围类型的紧凑约束条件

Question

我有一个字段，需要每个日期始终有一个有效的行。从文档中我了解到可以通过使用EXCLUDE USING gist来防止重叠。如何确保数据始终紧凑，即没有空隙？

CREATE TABLE observations (
    vintage daterange,
    value numeric,
    EXCLUDE USING gist (vintage WITH &&)
);

如何确保每个日期都有一个值？在实际应用中，存在第一个观测值，最新观测值的日期范围的第二个字段为null。

- lukevancleve

Answer 1

假设您按时间顺序输入数据，最好的选择可能是使用INSERT触发器。这是因为您不仅需要插入新数据，还需要在当前条目之前关闭最近条目的日期范围。

使用日期范围的解决方案

CREATE FUNCTION trf_bi_observations() RETURNS trigger AS $$
DECLARE
  recent_date date;
BEGIN
  -- Find the most recently entered date
  SELECT max(lower(vintage)) INTO recent_date
  FROM observations;

  -- Make sure the new date range makes sense: it has to be after the most recently
  -- entered date range and it has to have an open upper range.
  IF lower(NEW.vintage) <= recent_date OR NOT upper_inf(NEW.vintage) THEN
    RAISE NOTICE 'New vintage must be more recent than latest vintage and have an open upper bound';
    RETURN NULL; -- Fail the insert
  END IF;

  -- Update the most recent record to close the upper bound of vintage
  UPDATE observations
  SET vintage = daterange(recent_date, lower(NEW.vintage)) -- upper bound exclusive
  WHERE lower(vintage) = recent_date;

  -- All done, make the INSERT happen
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bi_observations
BEFORE INSERT ON observations
FOR EACH ROW EXECUTE PROCEDURE trf_bi_observations();

请注意，使用此INSERT触发器后，您不再需要EXCLUDE约束，因为触发器函数的逻辑不允许重叠。但是，您必须考虑更新和删除。如果您想禁止在此表上进行删除操作，可以简单地撤销权限或设置一个BEFORE DELETE触发器，什么也不做，只需RETURN NULL（这将使DELETE操作失败）。您必须允许更新（否则上面的插入触发器将失败），但可以将其限制为更改value列的值和关闭最近一行的上限。

-- OLD upper bound must be open, else disallow change to vintage column
IF upper_inf(OLD.vintage) THEN
  -- Only allow changes in upper bound
  NEW.vintage = daterange(lower(OLD.vintage), upper(NEW.vintage));
ELSE
  NEW.vintage = OLD.vintage -- silently ignore changes
END IF;

没有日期范围的解决方案

根据您的要求 - 没有重叠，没有间隙 - 您可以通过仅使用“vintage”列的date而不是daterange来大大简化数据模型。该范围始终可以从当前行的date到下一个角色的date构建。这隐含地满足了两个要求，并且触发器函数变得更加简单：

CREATE FUNCTION trf_bi_observations2() RETURNS trigger AS $$
BEGIN
  -- Make sure the new date makes sense: it has to be after the most recently
  -- entered date.
  PERFORM * FROM observations WHERE vintage >= NEW.vintage;
  IF FOUND THEN
    RAISE NOTICE 'New vintage must be more recent than latest vintage';
    RETURN NULL; -- Fail the insert
  END IF;

  -- All done, make the INSERT happen
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_bi_observations2
BEFORE INSERT ON observations
FOR EACH ROW EXECUTE PROCEDURE trf_bi_observations2();

现在你不必使用开放上界更新最近的行，因此可以禁止对“vintage”列进行任何更新。您还可以决定允许插入和更新任何日期，以便单个行的日期范围随此类事件而更改。由您决定。无论哪种情况，使用简单的date值比使用daterange更容易且更快。

您仍然可以使用正确的SELECT语句以daterange格式检索具有“vintage”的数据。

SELECT daterange(vintage, lead(vintage) OVER (ORDER BY vintage ASC)) AS vintage, value
FROM observations;

< p > lead() 窗口函数将为您提供窗口帧中下一行的 "vintage" 列的日期，这是由于排序而导致的下一个日期。

- Patrick

---

@lukevancleve 你好Luke，欢迎来到StackOverflow并很高兴能够帮助你。https://stackoverflow.com/help/someone-answers - Patrick