You might define new types for temperature, length, whatever, with suitable conversion, operation and creation functions. You’d be able to define how the new types participate in indexes, support directly sorting on them, so you package up this complexity and forget about it.
Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display).
Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified.
Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit
Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display).
Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified.
Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit
On Aug 14, 2021, 12:51 -0700, Gogala, Mladen <gogala.mladen@xxxxxxxxx>, wrote:
I would write a stable function converting everything to metric (or
imperial, depends on your preferences) and sort on the return of the
function. Since unit conversion functions do not need to modify the
database and should always return the same values for the same
arguments, the function can be used within a query (that is the meaning
of the keyword "STABLE") and you can use it for sorting stuff
Regards
On 8/14/2021 12:14 PM, Bret Stern wrote:
I have a table with metric, imperial, fraction columns.--
Is there a way to sort correctly using imperial (eg; .125, .375, .437
-> 1., 1.125)
Couldn't handle it with ORDER BY ASC, DESC args so I added a
sort_column and sorted based
on those values eg; 1,2,3,4,5,6 indicating the value I need to show in
which sequence.
Just curious what the pro's do
Bret
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Blog: https://dbwhisperer.wordpress.com
