Checking PostgreSQL Version in Scripts

psql(1) provides a little support to conditionals and this can be used in scripts to check, for instance, the PostgreSQL version.
This is quite trivial, however I had to adjust an example script of mine to act properly depending on the PostgreSQL version.

The problem

The problem I had was with declarative partitioning: since PostgreSQL 11, declarative partitioning supports a DEFAULT partition, that is catch-all bucket for tuples that don’t have an explicit partition to go into. In PostgreSQL 10 you need to manually create catch-all partition(s) by explicitly defining them.
In my use case, I had a set of tables partitioned by a time range (the year, to be precise), but I don’t want to set up a partition for each year before the starting point of clean data: all data after year 2015 is correct, somewhere there could be some dirty data with bogus years.
Therefore, I needed a partition to catch all bogus data before year 2015, that is, a partition that ranges from the earth creation until 2015. In PostgreSQL 11 this, of course, requires you to define a DEFAULT partition and that’s it! But how to create a different default partition on PostgreSQL 10 and 11?

I solved the problem with something like the following:

\if :pg_version_10

\echo 'PostgreSQL version is 10'
\echo 'Emulate a DEFAULT partition'

CREATE TABLE digikam.images_old
       PARTITION OF digikam.images_root
       FOR VALUES FROM ( MINVALUE )
                TO ( '2015-01-01' );

\else

\echo 'PostgreSQL version is at least 11'
\echo 'Using DEFAULT partition'

CREATE TABLE digikam.images_old
       PARTITION OF digikam.images_root
       DEFAULT;

\endif

The idea is quite simple: if (\if) PostgreSQL is at version 10 emulate a default partition, otherwise (\else) PostgreSQL is at version 11 or greater and can use native DEFAULT partition. The partition table is named the same in the two cases so that the final user does not see any difference.

But what is that :pg_version_10 stuff? That’s a boolean psql(1) variable set up by another utility, included into my script:

SELECT
EXISTS ( SELECT setting
         FROM   pg_settings
         WHERE  name = 'server_version_num'
         AND    setting::int >= 120000
         AND    setting::int  < 130000
       )
       AS pg_version_12
, EXISTS ( SELECT setting
         FROM   pg_settings
         WHERE  name = 'server_version_num'
         AND    setting::int >= 110000
         AND    setting::int  < 120000
         )
         AS pg_version_11
-- and so on ...
, EXISTS ( SELECT setting
         FROM   pg_settings
         WHERE  name = 'server_version_num'
         AND    setting::int < 100000
         )
         AS pg_version_less_than_10
\gset

The script does a very dummy job: it queries the server_version_num setting and dynamically creates (\gset) variables that are true depending on the PostgreSQL instance version number.
The only thing required is to import the script, for instance at the very top of your script, as for instance:

-- beginning of your script
\ir ../pgsql.check_postgresql_version.psql

And that’s all folks!

What this allows me to do is, for instance, avoid to run a declarative partition script at all if that is not supported on the server side:

\if :pg_version_less_than_10
\echo 'PostgreSQL version less than 10, cannot run declarative partitioning!'
\echo 'Update yourself!'
\quit
\endif

Just placing the above snippet on top of my declarative partitioning script prevents me to running commands that will generate errors if the server is not at least at version 10.

Summary

Thanks to psql(1) conditionals support it is possible to behave differently depending on the server version.
The advantage is that, clearly, you can build more robust scripts.
The drawback is that such script will require psql(1) and are therefore less portable.

The article Checking PostgreSQL Version in Scripts has been posted by Luca Ferrari on July 18, 2019

Tags: postgresql , planet-postgresql-org