I need to make sure that an available PostgreSQL version is not lower than required one. The version string could be requested as follows:
SELECT VERSION();
It returns me something like:
PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 64-bit
Theoretically I could parse this string, but I am not sure that future versions of PostgreSQL server will keep this word order.
Does PostgreSQL have some predictable version report possibly split in major and minor version number?
show server_version_num; --returns 90602::text
show server_version; --returns 9.6.2::text
https://blog.2ndquadrant.com/finding-postgresql-version/ says:
You can use that more easily within an SQL query like this
SELECT current_setting('server_version_num');
You can query the PostreSql View pg_settings which is present at pg_catalog
select * from pg_settings where name like '%version%';
The query above will bring you two settings:
name other columns .....
server_version .....
server_version_num .....
For your specific case you will want the following configuration:
select name, setting, min_val, max_val
from pg_settings
where name = 'server_version_num';
name setting min_val max_val
--------------------------------------------------
server_version_num 90503 90503 90503
From here you can work with min_val and max_val
This query is equivalent to the answer provided by @ŁukaszKamiński with some more detail (if you select all columns.)
