postgresql - 在 postgreSQL 数据库中加入 tstzrange

Question

我有一张带有 tstzrange、city 和布尔值的表格。现在我想将连续几天具有相同布尔值和城市的值合并在一起。所以例如我有

 city |                     date_range                          | bool 
-----+----------------------------------------------------------+--------
   berlin | ["2010-01-01 00:00:00+01","2010-01-01 00:00:00+01]  | True
   berlin | ["2010-01-02 00:00:00+01","2010-01-02 00:00:00+01"] | True
   berlin | ["2010-01-03 00:00:00+01","2010-01-03 00:00:00+01"] | False
   berlin | ["2010-01-04 00:00:00+01","2010-01-04 00:00:00+01"] | True
   paris  | ["2010-01-05 00:00:00+01","2010-01-05 00:00:00+01"] | True
   munich | ["2010-01-01 00:00:00+01","2010-01-01 00:00:00+01"] | True
   munich | ["2010-01-02 00:00:00+01","2010-01-02 00:00:00+01"] | False

期望的结果是

 city |                     date_range                          | bool 
-----+----------------------------------------------------------+--------
   berlin | ["2010-01-01 00:00:00+01","2010-01-02 00:00:00+01]  | True
   berlin | ["2010-01-03 00:00:00+01","2010-01-03 00:00:00+01"] | False
   berlin | ["2010-01-04 00:00:00+01","2010-01-04 00:00:00+01"] | True
   paris  | ["2010-01-05 00:00:00+01","2010-01-05 00:00:00+01"] | True
   munich | ["2010-01-01 00:00:00+01","2010-01-01 00:00:00+01"] | True
   munich | ["2010-01-02 00:00:00+01","2010-01-02 00:00:00+01"] | False

请注意，我不希望查询输出期望的结果，而是实际调整/合并 tstzranges 的函数

标签： postgresqljoinmerge