PostgreSQL: Convert software version string to integer

There are probably better ways to do this, however, one solution would be:

SELECT major + minor + patch AS app_version_converted FROM 
    (SELECT parts[1]::int * 10000 AS major, parts[2]::int * 100 AS minor, parts[3]::int AS patch FROM 
        (SELECT STRING_TO_ARRAY(app_version, '.') AS parts FROM version) AS p
    ) AS numerical;

One thing to note though is that what you are attempting is kind of questionable. Unless you intend to treat you version numbers as integers (i.e., you intend to do math or similar on them) you do most likely not want to do what you are trying to do.

Depending on the task at hand you might benefit from splitting your table into major, minor and patch for the versions, in which case you can argue for treating them as integers (since you probably add 1 for each new version).

If you have this need you can probably create a nice view along the lines of:

CREATE VIEW nive_version AS
    (SELECT parts[1]::int * 10000 AS major, parts[2]::int * 100 AS minor, parts[3]::int AS patch FROM 
        (SELECT STRING_TO_ARRAY(app_version, '.') AS parts FROM version) AS p
    )

This will also allow for sorting etc. Formatting them as one big number as you are attempting to here is as said very likely to be a bad idea