sorting/comparing column values in non-alphanumeric sorting ways ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

sorting/comparing column values in non-alphanumeric sorting ways ?

David Gauthier
Hi:

I have a table listing tools and tool versions for a number of different tool configurations.  Something like this...

create table tv (tool text, tcfg1 test, tcfg2 text, tcfg3 text, highestver text);

insert into tv (tool,tcfg1mtcfg2,tcfg3) values
('tool_a','1.0.5b','1.0.10','1.0.9');

I want to load the 'highestver' column with the highest version of tcfg1-3.

This won't work...
update tv set greatest = greatest(tcfg1,tcfg2,tcfg3)
...because it thinks 1.0.9 is greater than 1.0.10

Is there a way to get this to work right ?

Thanks in Advance !
Reply | Threaded
Open this post in threaded view
|

Re: sorting/comparing column values in non-alphanumeric sorting ways ?

David G Johnston
On Wednesday, July 11, 2018, David Gauthier <[hidden email]> wrote:

This won't work...
update tv set greatest = greatest(tcfg1,tcfg2,tcfg3)
...because it thinks 1.0.9 is greater than 1.0.10

Is there a way to get this to work right ?


Haven't used it personally but this seems promising:


David J.
Reply | Threaded
Open this post in threaded view
|

Re: sorting/comparing column values in non-alphanumeric sorting ways ?

Peter Geoghegan-4
In reply to this post by David Gauthier
On Wed, Jul 11, 2018 at 2:44 PM, David Gauthier
<[hidden email]> wrote:
> I want to load the 'highestver' column with the highest version of tcfg1-3.
>
> This won't work...
> update tv set greatest = greatest(tcfg1,tcfg2,tcfg3)
> ...because it thinks 1.0.9 is greater than 1.0.10
>
> Is there a way to get this to work right ?

If you're using v10 with ICU, then you can create a custom ICU
collation for this, with "natural" sort order. Something like this
should work:

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

See the docs -- "23.2.2.3.2. ICU collations".

--
Peter Geoghegan

Previous Thread Next Thread