Re: Sort question - Fractions, Metric etc

On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> > On 8/14/21 9:37 AM, Bret Stern wrote:
> >> I haven't explored doing this with numeric types, but some columns
> >> needed alpha chars eg 13mm.
>
> > Two columns:
>
> > data_val(numeric) data_unit(varchar)
> > 13 mm
>
> It sounds like your data is a completely disorganized mess :-(.
> You might be able to bring some semblance of coherence to it with
> an extension like postgresql-unit [1], but it will take a lot of
> effort to get the data into a representation that can sort reasonably.
> There's no "easy button" here.
>
> regards, tom lane
>
> [1] https://github.com/df7cb/postgresql-unit
>
>
I'm not sure, but maybe a kind of cast function that cast everything into a
reasonable common unit(Khm... millimeters) and sort on that? It seems
relatively simple to pick up the value and unit from a string with a
regexp. Admittedly the data would still be a mess.

Regards,
Sándor