Parsing YugabyteDB version()
Tags: yugabytedb, postgres, sql, database
Published: 2022-05-30 16:26:02

A PostgreSQL version is like `PostgreSQL ([0-9]+)[.]([0-9]+)` with the version and minor numbers:
```sql
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:
```sql
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](https://www.postgresql.org/docs/current/install-procedure.html).

## YugabyteDB

A YugabyteDB version is like `PostgreSQL ([0-9]+)[.]([0-9]+)-YB-.*` where the extra text includes `-YB-` followed by the YugabyteDB version :
```sql
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](https://github.com/yugabyte/yugabyte-db/blob/master/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:

```sql
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:
```sql
yugabyte=# 
select current_setting('server_version_num');   

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


```
Here is what I use to get the YugabyteDB version as a number:
```sql
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/