Postgres SUM over PARTITION BY “tolerate” null values

All we need is an easy explanation of the problem, so here it is.

I have a view which calculates rolling sum taking in value form previous row based on PARTITION BY clause, and I need to indicate one field in PARTITION BY clause can have nulls in them.

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Sometimes SupplierFacilityId is null and that’s fine, it should still be included in the sum() calculation.

Is this possible with Postgres?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your image from the comment (which should be text in the question) shows that you run the query with this WHERE clause:

WHERE ... AND ("SupplierFacilityId" IS NULL OR "SupplierFacilityId" = 647)

So you can just drop "SupplierFacilityId" from PARTITION BY to achieve what you want: rows with "SupplierFacilityId" IS NULL are treated as equal peers of the partition:

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RunningSum"

Also, since the default framing option is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and assuming that (s."LocationId", s."PartId", s."DueDate") is defined unique (which makes RANGE and ROWS equivalent in this context), you can just drop the custom framing options:

sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId"
                              ORDER BY s."DueDate") AS "RunningSum"

Without said WHERE clause, your goal would be unachievable with a simple window function as you would have to use the same rows with NULL values in multiple partitions. You would have to multiply those rows first …

Method 2

As ypercube mentioned in the comments, it should still work even when the SupplierFacilityId is null. But if you’re running into issues or want to force it to default to a specific value, you can use the COALESCE() function to replace the NULL value with something else.


Per your updated comments, here’s an example query that uses a simple CASE statement to hopefully achieve the logic of your image:

CASE 
    WHEN s."SupplierFacilityId" IS NULL THEN s."QuantityChange" 
    ELSE sum(s."QuantityChange") OVER (PARTITION BY s."LocationId", s."PartId", s."SupplierFacilityId" ORDER BY s."DueDate" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
END AS "RunningSum"

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply