Stringent unique constraints on time series data tables

[mactyr]

As an energyPATHWAYS developer
I want tight constraints on my time series data tables
so that I don't accidentally set up conflicting or duplicate data while working on my models

AC

1. All tables that store time-series data (e.g. SupplyTechsInstallationCostsNewData, SupplyTechsVariableMaintenanceCostData) should have a multi-column unique constraint that covers all columns except for "value". It would be wise to review the full list of constraints with @BenHaleyEvolvedEnergyResearch before implementing.

Future Work

This ticket is to document a particular class of helpful constraints that came up in discussion today. We should review other kinds of tables to see what additional constraints would be helpful as well.

[mactyr]

It would be efficient to do this after we consolidate the database tables so we only have a few tables to add constraints to rather than many.

[mactyr]

Note that by default postgres treats NULL values as always being unequal to each other (at least for the purposes of unique constraints) meaning that if you apply a unique constraint across several columns duplicates will be allowed as long as at least one of the columns is NULL in both rows! This is a common situation for us since, e.g., oth_1_id or oth_2_id will often be NULL. So a simple unique constraint won't do much good for many of our tables.

It is possible but not trivial to work around this using partial indexes or by coalescing NULLs to some non-null value in the index (-1 might work nicely for us since the things we are trying to enforce uniqueness on -- foreign keys, years, etc. -- generally won't be negative). See the first couple of answers here.