Unique constraint keys not invoked when a field is NULL

I have a table named AQI, with the following column

Only one unique value of date, pollutant_code, source_id and time are able to exist, so I defined a unique constraint source_code_time_uq.

And the upsert query like

INSERT INTO aqi(source_id, source_type, pollutant_code, "date", "time", aqi_value)
VALUES (4, 1 ,'PM2.5','2018-05-28',8, 789)
ON CONFLICT ON CONSTRAINT source_code_time_uq
DO UPDATE SET
    aqi_value = 789

The upsert method work normally when all the field available, but when I put NULL in the time column (in an attempt to make that row represent AQI data for a whole day), the source_code_time_uq constraint doesn’t invoked and it still insert a new row.

So how can I add unique constraint check on the field that can also be NULL , do I need to update my upsert query ?

Answer

You can use a filtered unique index:

create unique index unq_aqi_4 on (date, pollutant_code, source_id, time)
    where time is not null;

Then, you probably also want to guarantee only one NULL row per day, so:

create unique index unq_aqi_4 on (date, pollutant_code, source_id)
    where time is null;

Of course, remove the unique constraint as well.