Re: Get PG primary version without substring manipulation?

2024年6月13日(木) 22:18 Keith Fiske <keith.fiske@crunchydata.com>:
>
>
>
> On Thu, Jun 13, 2024 at 8:52 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>>
>> cut, awk, head, bash, etc are easy enough,  but am I overlooking some intrinsic method of getting just PG's primary
versionnumber (9.6 or 14, in my case) without doing substring manipulation? 
>>
>> $ ssh foo.example.com pg_config --version
>> PostgreSQL 9.6.24
>> $ ssh foo.example.com psql --version
>> psql (PostgreSQL) 9.6.24
>> $ psql -h foo.example.com -Xtc "SELECT version();"
>>  PostgreSQL 9.6.24 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
>>
>> $ ssh bar.example.com pg_config --version
>> PostgreSQL 14.12
>> $ ssh bar.example.com psql --version
>> psql (PostgreSQL) 14.12
>> $ psql -h bar.example.com -Xtc "SELECT version();"
>>  PostgreSQL 14.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

Be aware that the --version output of binary files tells you the version number
of the first matching binary in the shell path, but there's no guarantee that
this will be the same as the version of the running server.

> If you're logging into the database, I prefer getting the number value like this when using it for comparisons. Still
needto do some manipulation if you just want the major version, but it's a lot easier to work with than the strings you
weregetting with other methods. 
>
> keith=# select current_setting('server_version_num')::int;
>  current_setting
> -----------------
>           160002

How about:

SELECT array_to_string(numbers[1 : array_upper(numbers,1) -1], '.')
  FROM
(SELECT string_to_array(current_setting('server_version'), '.') numbers) n;

Regards

Ian Barwick