Re: Sort question - Fractions, Metric etc - Mailing list pgsql-general

From bret_stern@machinemanagement.com
Subject Re: Sort question - Fractions, Metric etc
Date
Msg-id 20210814164221.CD2E8A0FF1@mail.machinemanagement.com
Whole thread Raw
In response to Re: Sort question - Fractions, Metric etc  (Sándor Daku <daku.sandor@gmail.com>)
List pgsql-general
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@gmail.com>
Date: 8/14/21 10:19 AM (GMT-08:00)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Bret Stern <bret_stern@machinemanagement.com>, pgsql-general@lists.postgresql.org
Subject: Re: Sort question - Fractions, Metric etc



On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.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

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Sort question - Fractions, Metric etc
Next
From: Open _
Date:
Subject: Understaning postgresql instance vs database