PostgreSQL execute statement conditionally by server version

Question

I'm currently writing some installer script that fires SQL files against different database types depending on the system's configuration (the webapplication supports multiple database server like MySQL, MSSQL and PostgreSQL).

One of those types is PostgreSQL. I'm not fluent with it and I would like to know if it's possible to make a statement into a define/populate SQL file that makes an SQL query conditional to a specific PostgreSQL server version.

How to make an SQL statement conditionally in plain PGSQL so that it is only executed in version 9? The command is:

ALTER DATABASE dbname SET bytea_output='escape';

The version check is to compare the version with 9.

Answer 1

Or you could just use

select setting from pg_settings where name = 'server_version'

Or

select setting from pg_settings where name = 'server_version_num'

If you need major version only

select Substr(setting, 1, 1) from pg_settings where name = 'server_version_num'

or

select Substr(setting, 1, strpos(setting, '.')-1) from pg_settings where name = 'server_version'

if you want it to be compatible with two digit versions.