Обсуждение: sorting/comparing column values in non-alphanumeric sorting ways ?
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 !
On Wednesday, July 11, 2018, David Gauthier <davegauthierpg@gmail.com> wrote:
This won't work...update tv set greatest = greatest(tcfg1,tcfg2,tcfg3)...because it thinks 1.0.9 is greater than 1.0.10Is there a way to get this to work right ?
Haven't used it personally but this seems promising:
David J.
On Wed, Jul 11, 2018 at 2:44 PM, David Gauthier <davegauthierpg@gmail.com> 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