News for hackers. Stuff that matters.
Re:Postgres 15 improves UNIQUE and NULL by RedShift1 on Monday July 11, 2022 @05:40AM (#32054032)
What about this, let's say I have two electricity meters. They have bunch of fields (active power, reactive power, voltage, current, and all that times 3 (one such measurement for each phase)), but one kind of meter can measure distortions, the other can not.

I would make the distortion columns nullable for the pratical reason that you either have to duplicate all the work for querying the same properties from the meters (for example a dashboard that shows voltages only). If you want to support both types of meters in the same dashboard you would have to do a UNION type query (and not ever forget to do that!) if you store the measurements in a different table.

Re:Postgres 15 improves UNIQUE and NULL by rocqua on Monday July 11, 2022 @05:51AM (#32054093)
It's possible to handle these kinds of fields (at most one value) with separate tables and foreign keys. It has advantages around 'proving correctness'. But without extra tooling it has significant usability, and perhaps performance disadvantages. I wonder if you can get around some of this using virtual tables.

I come at this with little experience with SQL, and having worked a bit on ampersand[1], a tool where you declare the structure of your data, and some invariants, and the tool will create a schema for your database, and some automatic checking to ensure your invariants are upheld.

[1] https://ampersandtarski.gitbook.io/documentation/

Re:Postgres 15 improves UNIQUE and NULL by cwillu on Monday July 11, 2022 @07:12AM (#32054593)
Rather than putting the two types of meters in two different tables and doing an error-prone UNION, you would put the distortion measurements in a different table joined to the first, and the meters that don't support it would simply not include entries in that table.
Re:Postgres 15 improves UNIQUE and NULL by RedShift1 on Monday July 11, 2022 @07:36AM (#32054751)
But that would still get you nulls in the result set, which is what we're trying to avoid?
Re:Postgres 15 improves UNIQUE and NULL by synthmeat on Monday July 11, 2022 @06:42AM (#32054412)
Boolean capabilities of meter per every entry? Shouldn't impact size that much, easy to index. As a bonus, you can track failure of a sensor per each meter, and know when it first failed.
Re:Postgres 15 improves UNIQUE and NULL by jve on Monday July 11, 2022 @07:00AM (#32054522)
Oh yeah, solve complexity by introducing complexity squared.
Re:Postgres 15 improves UNIQUE and NULL by Cthulhu_ on Monday July 11, 2022 @05:55AM (#32054126)
Wouldn't a meter value that doesn't have distortions just have a value of 0 (that is, not null but just zero); the properties of a meter in this case belong somewhere else, either in a "meter properties" table if you have many meters with many properties that can change, or just hardcoded.

Alternatively, a key / value table, e.g. `meter_id, property, value`. But that isn't very optimal, works better for things like a shop product with many different properties per product.

Re:Postgres 15 improves UNIQUE and NULL by RedShift1 on Monday July 11, 2022 @06:18AM (#32054280)
It doesn't mean there are no distortions, it just means they are not measured. So I vastly prefer knowing there's no data than some placeholder that "simulates" no data