Postgres upsert使用复合唯一键，只允许单个空值

Question

作为ETL的一部分，表continuous_trips具有连续的传入记录流
新记录被聚合并被插入temp。名为trips_agg的表每5分钟一次。

CREATE TABLE IF NOT EXISTS trips_agg AS (
SELECT start_time, station_id, from_station, to_station, from_terminus, end_terminus, previous_station, next_station,
    AVG(wait_span) AS wait_span,
    AVG(walk_span) AS walk_span,
    AVG(delay_span) AS delay_span,
    SUM(passengers_requests) AS passengers_requests
  FROM continuous_trips
  GROUP BY start_time, station_id, from_station, to_station, from_terminus, end_terminus, previous_station, next_station
)

将所有记录插入表daily_trips并在下一个循环中重新创建后，表trips_agg将被删除
表格每日行程

CREATE TABLE IF NOT EXISTS daily_trips  ( 
             start_time timestamp without time zone NOT NULL,
             station_id text NOT NULL, 
             from_station text NOT NULL,
             to_station text NOT NULL,
             from_terminus text NOT NULL,
             end_terminus text NOT NULL,
             previous_station text,                                
             next_station text,                                
             wait_span interval NOT NULL,
             walk_span interval NOT NULL,
             delay_span interval NOT NULL,
             passengers_requests numeric NOT NULL
             )

注意：列previous_station和next_station允许为空。
复合唯一键添加如下：

ALTER TABLE daily_trips ADD CONSTRAINT daily_trips_unique_row UNIQUE   
(start_time, station_id, from_station, to_station, from_terminus, end_terminus, previous_station, next_station);

如果插入时违反了唯一密钥，则应更新记录。所以使用了upsert策略。

INSERT INTO daily_trips SELECT * FROM trips_agg  
ON CONFLICT (start_time, station_id, from_station, to_station, from_terminus, end_terminus,  
previous_station, next_station) DO UPDATE     
set wait_span = (daily_trips.wait_span + EXCLUDED.wait_span)/2,   
walk_span = (daily_trips.walk_span + EXCLUDED.walk_span)/2 ,  
delay_span = (daily_trips.delay_span + EXCLUDED.delay_span)/2,   
passengers_requests =(daily_trips.passengers_requests + EXCLUDED.passengers_requests); 

当所有列的值都存在时，此设置可以完美地工作，但当任何可为空的列都有空值时，情况并非如此
因为PoSGRESs不考虑NULL值来调用唯一的约束，每当任何空的列都有NULL值时，插入一个新行，而不是更新。这将导致为唯一键生成多行
为了克服这一问题，在参考本文后，在表每日行程中添加了一个索引。

create unique index daily_trips_unique_trip_idx ON daily_trips  
(start_time, station_id, from_station, to_station, from_terminus, end_terminus,   
(previous_station IS NULL), (next_station IS NULL)   
where previous_station IS NULL or fnext_station IS NULL

但是，对于任何可为null的列，只能添加一行，其中包含null值。对于任何可为null的列，下一行的值为null，更新不会发生，而是出现以下错误：

ERROR:  duplicate key value violates unique constraint "daily_trips_unique_trip_idx"

需要什么
应遵守唯一约束，当“上一个_站”或“下一个_站”这两个可为空的列中有空值时，应进行更新<感谢您的帮助。

Answer 1

解决方案是将NULL转换为其他值，更具体地说，是长度为0的字符串 (''). 当作为coalesce（column_name, '')使用时，coalesce函数恰好做到了这一点。问题是创建一个唯一的约束，从而产生语法错误。因此您无法创建该约束。然而，有一个解决方案，尽管并不容易。Postgres通过唯一的索引强制执行唯一的约束，因此只需直接创建索引。

create unique index daily_trips_unique_row on daily_trips 
           ( start_time
           , station_id
           , from_station
           , to_station
           , from_terminus
           , end_terminus
           , coalesce(previous_station , '')
           , coalesce(next_station, '')
           );  

然而，尽管上面提到了索引列的空功能，但它不再识别INSERT。。。关于冲突（参见此处示例）。您需要一个函数/过程来处理异常，或者使用Select。。。如果存在，则更新else Insert逻辑。