Sort question - Fractions, Metric etc

On 8/14/21 9:14 AM, 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)

Alright how is this different from metric or fraction?

I can sort of see fraction if you mean as 1/3, 1/20, etc.

What is the data type of the field you are storing this in?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

Strings;

I haven't explored doing this with numeric types, but some columns
needed alpha chars eg 13mm.

Although I could have front ended this UI with mm nomenclature I did not.

I'll put a table together with appropriate numeric types and see if the
sort will behave.

On 8/14/21 9:37 AM, Bret Stern wrote:

Strings;

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

Although I could have front ended this UI with mm nomenclature I did not.

I'll put a table together with appropriate numeric types and see if the
sort will behave.

It will.

On 8/14/2021 9:21 AM, Adrian Klaver wrote:

On 8/14/21 9:14 AM, 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)

Alright how is this different from metric or fraction?

I can sort of see fraction if you mean as 1/3, 1/20, etc.

What is the data type of the field you are storing this in?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

I like that idea

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]https://github.com/df7cb/postgresql-unit, 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

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

On 8/14/21 10:19 AM, Sándor Daku wrote:

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.

I see the potential for another crash landing:) :

https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_failure

Regards,
Sándor

--
Adrian Klaver
adrian.klaver@aklaver.com

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]https://github.com/df7cb/postgresql-unitI&#39;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, 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-unitI&#39;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

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

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

Here's the clip of the UI. The user selects whatever value the vendor
provides for unit thickness. The data entry

people aren't comfortable converting.

At this point the sort_order column managed to do the trick....but going
forward on my next

application, I can see a different approach.

Are you suggesting a function like this shellsort used with an array.

https://www.geeksforgeeks.org/shellsort/

// C++ implementation of Shell Sort

#include <iostream>
using namespace std;

/* function to sort arr using shellSort */
int shellSort(int arr[], int n)
{
    // Start with a big gap, then reduce the gap
    for (int gap = n/2; gap > 0; gap /= 2)
    {
        // Do a gapped insertion sort for this gap size.
        // The first gap elements a[0..gap-1] are already in gapped order
        // keep adding one more element until the entire array is
        // gap sorted
        for (int i = gap; i < n; i += 1)
        {
            // add a[i] to the elements that have been gap sorted
            // save a[i] in temp and make a hole at position i
            int temp = arr[i];

            // shift earlier gap-sorted elements up until the correct
            // location for a[i] is found
            int j;
            for (j = i; j >= gap && arr[j - gap] > temp; j -= gap)
                arr[j] = arr[j - gap];

            // put temp (the original a[i]) in its correct location
            arr[j] = temp;
        }
    }
    return 0;
}

void printArray(int arr[], int n)
{
    for (int i=0; i<n; i++)
        cout << arr[i] << " ";
}

int main()
{
    int arr[] = {12, 34, 54, 2, 3}, i;
    int n = sizeof(arr)/sizeof(arr[0]);

    cout << "Array before sorting: \n";
    printArray(arr, n);

    shellSort(arr, n);

    cout << "\nArray after sorting: \n";
    printArray(arr, n);

    return 0;
}

Array before sorting:
12 34 54 2 3
Array after sorting:
2 3 12 34 54

On 8/14/21 1:24 PM, Bret Stern wrote:

Here's the clip of the UI. The user selects whatever value the vendor
provides for unit thickness. The data entry

So the vendors supply the measurements in all the various units for a
given item?

people aren't comfortable converting.

At this point the sort_order column managed to do the trick....but going
forward on my next

application, I can see a different approach.

Are you suggesting a function like this shellsort used with an array.

--
Adrian Klaver
adrian.klaver@aklaver.com

Yep,

I provide the UI for the user to select the vendor supplied unit value
from the list.

I don't want anyone entering (manually) any value, they must select from
my list (fed from a PG table).

(and since some units don't translate exactly, they pick the one that is
closest).

It's Tile (floor, shower etc), not the tightest tolerance of dimensions,
so it's not catastrophic if it's off

a 32nd or so.

cheers

On 8/14/21 2:04 PM, Bret Stern wrote:

Yep,

I provide the UI for the user to select the vendor supplied unit value
from the list.

I should have been more specific, does the vendor do all the conversions
and supply them to you?

I don't want anyone entering (manually) any value, they must select from
my list (fed from a PG table).

(and since some units don't translate exactly, they pick the one that is
closest).

Yes, nominal dimensions. That is what I'm trying to work out, is this
something that makes sense to the vendor and should not be tampered with?

It's Tile (floor, shower etc), not the tightest tolerance of dimensions,
so it's not catastrophic if it's off

Is it only tile you are working with?

a 32nd or so.

cheers

--
Adrian Klaver
adrian.klaver@aklaver.com

On 8/14/2021 2:13 PM, Adrian Klaver wrote:

On 8/14/21 2:04 PM, Bret Stern wrote:

Yep,

I provide the UI for the user to select the vendor supplied unit
value from the list.

I should have been more specific, does the vendor do all the
conversions and supply them to you?

Vendors supply dimensions, depending on where in the world the product
comes from, could be metric, imperial

or fraction

I don't want anyone entering (manually) any value, they must select
from my list (fed from a PG table).

(and since some units don't translate exactly, they pick the one that
is closest).

Yes, nominal dimensions. That is what I'm trying to work out, is this
something that makes sense to the vendor and should not be tampered with?

The vendor is out of the picture at this point. They provide a catalog,
and we enter (or import) items into our POS system. We match their
dimension within tolerance.

It's Tile (floor, shower etc), not the tightest tolerance of
dimensions, so it's not catastrophic if it's off

Is it only tile you are working with?

No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks
and delivered in blocks of ten slabs normally, where tolerances are
also  +/- .032

I will say this business has been behind in the attributes game. Plus
there are many "artsy" vendors

who can hardly speak in these terms, and don't publish to us, so we do
the best we can.

Getting vendors to supply the basic values is a struggle.

On 8/14/21 2:47 PM, Bret Stern wrote:

On 8/14/2021 2:13 PM, Adrian Klaver wrote:

On 8/14/21 2:04 PM, Bret Stern wrote:

Vendors supply dimensions, depending on where in the world the product
comes from, could be metric, imperial

or fraction

Yes, nominal dimensions. That is what I'm trying to work out, is this
something that makes sense to the vendor and should not be tampered with?

The vendor is out of the picture at this point. They provide a catalog,
and we enter (or import) items into our POS system. We match their
dimension within tolerance.

It would seem then a entry system that specifies the initial input
unit(metric, imperial) and then does the conversion to a set unit(my
preference would be metric) for storage to the table in that unit. Then
you would convert on the fly to fill out the display field. Or you could
use something like generated
columns(https://www.postgresql.org/docs/12/ddl-generated-columns.html)
available in Postgres 12+ to pre-fill columns to save the overhead on
subsequent queries. For pre-12 maybe a trigger on the table column to
fill in the other columns. In any case you sort(order by) by your
canonical column which would be of numeric type.

No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks
and delivered in blocks of ten slabs normally, where tolerances are
also  +/- .032

I will say this business has been behind in the attributes game. Plus
there are many "artsy" vendors

who can hardly speak in these terms, and don't publish to us, so we do
the best we can.

Getting vendors to supply the basic values is a struggle.

a 32nd or so.

cheers

--
Adrian Klaver
adrian.klaver@aklaver.com

On 15 Aug 2021, at 7:47, Bret Stern wrote:

I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can.

Getting vendors to supply the basic values is a struggle.

I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how much (mostly in units of dollar) 😉
After that there may be length width and thickness values specified with (hopefully) the same units ± weight (per unit or box?) ± allowance for grout/joining (in case your application is going to be used as an aid in estimating quantities needed)
The truly artistic supplier will refrain from making anything the same and your customer will be expected to buy the item first and plan their project around it.

One possible data wrangling scheme would be to give each item a catalogue number (yours) — Just noticed Adrain saying something similar so apologies for the overlap.
The vendor_spec table would capture the vendor’s identifier, description and supplied dimensions (with a units column).
The dimensions_view (suggest a materialised view) would carry the dimension information in converted form, e.g., mm: numeric(8,3)
Once the conversions are setup the customer can be supplied with dimensions in the system of their choice and you have a sensible common point reference for any rankings.

When I first saw this thread I thought you were getting into metal dimensions and started to wonder if your next phase was to “organise” bolts according to diameter length and thread… you have picked the easier course, the other is a nightmare, e.g., https://www.americanmachinetools.com/machinist_tables.htm

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920

On 8/14/21 4:05 PM, Gavan Schneider wrote:

On 15 Aug 2021, at 7:47, Bret Stern wrote:

I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can.

Getting vendors to supply the basic values is a struggle.

I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how much (mostly in units of dollar) 😉

Hmm, you are lucky:)

Of late I get 'who knows' for how many and 'we'll see' for how much.

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920

--
Adrian Klaver
adrian.klaver@aklaver.com

I have a table with metric, imperial, fraction columns.

Have "metric_equivalent_magnitude" and "metric_equivalent_unit"
columns and do all your sorting &c. via those columns. Use the
relatively new generated (or computed/calculated) columns to calculate
these. That way, you get the conversion correct once and you don't
have to worry about it any more!

That way, you can compare between cubits, furlongs and kilometres or
barrels, hogsheads and litres & American and British imperial units.

You might also want a metric_equivalent_type to ensure that you're not
comparing lengths with volumes, masses or velocities... &c...?

Pól...