CODEWITHSUNDEEP
postgresqlversioning
Paul
Paul

Reputation: 26640

Get comparable PostgreSQL version number

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?

Upvotes: 4

Views: 2019

Answers (3)

Paul
Paul

Reputation: 26640

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');

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23361

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.)

Upvotes: 0

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

show server_version_num; --returns 90602::text
show server_version; --returns 9.6.2::text

Upvotes: 5

Related Questions