3 ms·
Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.
by ellisv 4y ago
Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.
- WirelessGigabit 4y agoEven when the column is made with NOT NULL?
- exac 4y agoNo.
- masklinn 4y agoPostgres uses distinct nulls. I've not checked, but I'd assume postgres simply does not index nulls, as it can't find them again anyway (unless you use the new "NULLS NOT DISTINCT" anyway). I think you need a separate index on the boolean IS NULL (which should probably be a partial index on whichever of IS NULL and IS NOT NULL is better).
- ellisv 4y agoYou're correct, thanks for noting this – I had it backwards.
- marcosdumay 4y agoPostgres absolutely adds nulls to its indexes. You can even control how they are ordered, and on the last few versions if nulls are equal or not. A complete index over a column will have entries for all records, and can be used on "x is null" and "x is not null" filters.