postgresql - 在 PostgreSQL 的表中添加缺失日期

Question

我有一个表，其中包含 2002 年每一天的数据，但缺少一些日期。即，2002 年有 354 条记录(而不是 365 条)。对于我的计算，我需要在表中包含空值的缺失数据

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |  65.6      | 2002-05-09 |
| 103 |  75.9      | 2002-05-10 |
+-----+------------+------------+

您看到 2002-05-08 不见了。我希望我的决赛 table 是这样的:

+-----+------------+------------+
| ID  |  rainfall  | date       |
+-----+------------+------------+
| 100 |  110.2     | 2002-05-06 |
| 101 |  56.6      | 2002-05-07 |
| 102 |            | 2002-05-08 |
| 103 |  65.6      | 2002-05-09 |
| 104 |  75.9      | 2002-05-10 |
+-----+------------+------------+

有没有办法在 PostgreSQL 中做到这一点？

如果我得到结果只是作为查询结果(不一定是更新表)并不重要

Answer 1

date 是 reserved word在标准 SQL 中是数据类型的名称，在 PostgreSQL 中是数据类型的名称。 PostgreSQL 允许它作为标识符，但这并不是一个好主意。我改用 thedate 作为列名。

不要依赖代理 ID 中没有间隙。这几乎总是一个坏主意。将这样的 ID 视为没有意义的唯一数字，即使它看起来大部分时间 都带有某些其他属性。

在这种特殊情况下，作为 @Clodoaldo commented ，thedate 似乎是一个完美的主键，而列 id 只是笨拙的 - 我删除了它:

CREATE TEMP TABLE tbl (thedate date PRIMARY KEY, rainfall numeric);
INSERT INTO tbl(thedate, rainfall) VALUES
  ('2002-05-06', 110.2)
, ('2002-05-07', 56.6)
, ('2002-05-09', 65.6)
, ('2002-05-10', 75.9);

查询

查询全表:

SELECT x.thedate, t.rainfall  -- rainfall automatically NULL for missing rows
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
LEFT   JOIN tbl t USING (thedate)
ORDER  BY x.thedate

类似于@a_horse_with_no_name已发布，但简化并忽略了修剪后的 id。

填充表中第一个和最后一个日期之间的空白。如果可能存在领先/滞后差距，请相应地扩展。您可以像 @Clodoaldo 这样使用 date_trunc()演示 - 但他的查询存在语法错误并且可以更简单。

插入缺失的行

最快和最易读的方法是NOT EXISTS anti-semi-join。

INSERT INTO tbl (thedate, rainfall)
SELECT x.thedate, NULL
FROM (
   SELECT generate_series(min(thedate), max(thedate), '1d')::date AS thedate
   FROM   tbl
   ) x
WHERE NOT EXISTS (SELECT 1 FROM tbl t WHERE t.thedate = x.thedate)