Comparison string in Postgresql

Question

My table:

CREATE TABLE public.software
(
    software_name text COLLATE pg_catalog."default",
    version text COLLATE pg_catalog."default", 
)

In that table I store software and their versions which I use in some projects and I check whenever they updated by comparing their version. I need to store the version informations as string because some versions contains strings like "1.0.1-beta".

I still couldn't find the best way to compare the strings. I use following (simplified) query:

    SELECT '1.2.3' < '1.2.4' -- true


    SELECT '1.2.3a' < '1.2.4B'

When i run following query:

select '3.0.0' > '26752' -- true

select '1.2.3a' < '1.2.3A' -- true

select 'i dont know' >= '2' --true

But 3.0.0 is smaller than 26752. 1.2.3a is (maybe) false, if I use lower() function I can solve it. How about select '3.0.0' > '26752' or the last query?

If you didn't have non-numeric parts, this would as easy as `select string_to_array('3.0.0','.')::int[] > string_to_array('26752','.')::int[]` — , Feb 25 '20 at 11:11
If you are okay to have a extension then check this [semver](https://pgxn.org/dist/semver/doc/semver.html) — Arun Palanisamy, Feb 25 '20 at 11:12
I can't install semver because it gave me the error "semver.control not found". Then i copied all files from shared to main extension directory. no i'm getting semver not found.. i think have to also copy lib directory but cant find it — Micheal Toru, Feb 25 '20 at 12:22

score 0 · Answer 1 · answered Feb 25 '20 at 11:49

0

In general, the best solution for that is to translate version numbers into something numerical.

The best I can offer you is an ICU collation for “natural comparison” (available from PostgreSQL v10 on):

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

ALTER TABLE public.software
   ALTER version TYPE text COLLATE en_natural;

That will make a best effort.

Of course, 1.0.1 will still be less than 1.0.1-beta3, as well as less than 1.0.1a, because there is no built-in knowledge that “beta” is less than an empty string, while other strings are not.

answered Feb 25 '20 at 11:49

Laurenz Albe

209,280
17
206
263

Thanks, but according to semver standards 1.0.1 is bigger than 1.0.1-beta3. – Micheal Toru Feb 27 '20 at 22:34
1

Then you'll probably have to use the extension. – Laurenz Albe Feb 28 '20 at 06:45

score 0 · Accepted Answer · answered Feb 28 '20 at 17:10

0

I installed semver extension, which works great! thanks @arun

answered Feb 28 '20 at 17:10

Micheal Toru

422
4
28

Comparison string in Postgresql

2 Answers2