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 problemThe problem I had was with declarative partitioning: since PostgreSQL 11, declarative partitioning supports a
DEFAULTpartition, 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
DEFAULTpartition 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:
The idea is quite simple: if (
\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
\if) PostgreSQL is at version 10 emulate a default partition, otherwise (
\else) PostgreSQL is at version 11 or greater and can use native
DEFAULTpartition. 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_10stuff? That’s a boolean
psql(1)variable set up by another utility, included into my script:
The script does a very dummy job: it queries the
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
server_version_numsetting 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:
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.
\if :pg_version_less_than_10 \echo 'PostgreSQL version less than 10, cannot run declarative partitioning!' \echo 'Update yourself!' \quit \endif
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.