Parsing YugabyteDB version()

A PostgreSQL version is like PostgreSQL ([0-9]+)[.]([0-9]+) with the version and minor numbers:

postgres=> select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit
(1 row)

postgres=>

but can also be PostgreSQL ([0-9]+)[.]([0-9]+).* with an additional text:

postgres=# select version();
                                                       version

-------------------------------------------------------------------------------------------------------
--------------
 PostgreSQL 15devel on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4.0.1), 64-bit
(1 row)

The devel string has been added at compile time with --with-extra-version option of configure.

YugabyteDB

A YugabyteDB version is like PostgreSQL ([0-9]+)[.]([0-9]+)-YB-.* where the extra text includes -YB- followed by the YugabyteDB version :

yugabyte=> select version();
                                                                                         version                                                                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2-YB-2.13.2.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 12.0.1 (https://github.com/yugabyte/llvm-project.git bdb147e675d8c87cee72cc1f87c4b82855977d94), 64-bit
(1 row)

yugabyte=> 

The PostgreSQL version identifies the compatibility (as YugabyteDB reuses PostgreSQL code), 11.2 in this case. The YugabyteDB version has a major and minor component, a patch number and build number. The minor is even for stable releases and odd for preview ones. This is 2.13.2 which is the latest preview at the time I'm writing this. The build number is not set here (the text behind -YB- comes from the version.txt file in the source branch which is version.minor.patch.

regexp_replace

I use the following to extract the YugabyteDB version and the PostgreSQL compatibility:

yugabyte=# \x
Expanded display is on.

yugabyte=# select version()
     , regexp_replace(version()
     ,'^PostgreSQL ([0-9]+)[.]([0-9]+)-YB-([0-9]+)[.]([0-9]+)[.]([0-9]+)[.]([0-9]+)-b([0-9]+).*'
     ,'\1.\2')       as pg_version
     , regexp_replace(version()
     ,'^PostgreSQL ([0-9]+)[.]([0-9]+)-YB-([0-9]+)[.]([0-9]+)[.]([0-9]+)[.]([0-9]+)-b([0-9]+).*'
     ,'\3.\4.\5')    as yb_version;

-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
version    | PostgreSQL 11.2-YB-2.13.2.0-b0 on aarch64-unknown-linux-gnu, compiled by clang version 12.0.1 (https://github.com/yugabyte/llvm-project.git bdb147e675d8c87cee72cc1f87c4b82855977d94), 64-bit
pg_version | 11.2
yb_version | 2.13.2

ordered number

This is a was to extract the version components. But when you want to compare version, better use one number. The PostgreSQL version can be queried with:

yugabyte=# 
select current_setting('server_version_num');   

                                             current_setting
-----------------
 110002

Here is what I use to get the YugabyteDB version as a number:

yugabyte=# 
with n(p,f) as (values ('\3',4),('\4',4),('\5',4))
select to_number(string_agg(lpad(regexp_replace(version()
,'^PostgreSQL ([0-9]+)[.]([0-9]+)-YB-([0-9]+)[.]([0-9]+)[.]([0-9]+)[.]([0-9]+)-b([0-9]+).*'
,p),f,'0'),'' order by p),'999999999999') yb_version_num from n;

 yb_version_num
----------------
      200130002
(1 row)

More about release versioning: https://docs.yugabyte.com/preview/releases/versioning/