Search code examples
postgresqlpostgresql-12

Comparison string in Postgresql

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?

Solution

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