Re: Sort question - Fractions, Metric etc

["bret_stern@xxxxxxxxxxxxxxxxxxxxx" <bret_stern@xxxxxxxxxxxxxxxxxxxxx>]

Of course it's a complete disorganized mess..

That'a the world I live in.

Best thing is, I'm walking away with some new strategies.

You guys rock

-------- Original message --------

From: Sándor Daku <daku.sandor@xxxxxxxxx>

Date: 8/14/21 10:19 AM (GMT-08:00)

To: Tom Lane <tgl@xxxxxxxxxxxxx>

Cc: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>, Bret Stern <bret_stern@xxxxxxxxxxxxxxxxxxxxx>, pgsql-general@xxxxxxxxxxxxxxxxxxxx

Subject: Re: Sort question - Fractions, Metric etc

On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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