Finding your PostgreSQL version

Questions

How do you tell what version of PostgreSQL you are using?
What is the version number of your PostgreSQL server?
What’s the release of your Postgres server?
What release is your Postgres database?
How do I write a program that acts differently for different versions of the PostgreSQL server?

Answer

For a long time it’s been possible to write a query to find out what version your server is.

SELECT version();

or you could look at that information by the preset parameter

SHOW server_version;

Both of those mechanisms refer to an internal string, accessible to C programs as PG_VERSION

Since PostgreSQL 8.2 we’ve been able to access the version number in a numeric form, allowing us to write programs that depend upon version number for any server version from 8.2 upwards to current versions and later.

SHOW server_version_num;

You can use that more easily within an SQL query like this

SELECT current_setting(‘server_version_num’);

postgres=# SELECT current_setting('server_version_num');
current_setting
-----------------
90600
(1 row)

Or you can access this value more easily using the PostgreSQL C API, PQserverVersion()
http://www.postgresql.org/docs/current/static/libpq-status.html

int PQserverVersion(const PGconn *conn);

These mechanisms access the internal macro PG_VERSION_NUM

These numeric values are much easier to use programmatically, allowing you to write programs that vary their behaviour across releases.

e.g. if (version < 90500)

This is mainly useful for accessing in-core functions or in-core Catalog Views.

These versions will be maintained when we go to release 10.0, so we expect this…

postgres=# SELECT current_setting('server_version_num');
current_setting
-----------------
100000
(1 row)

so that 10.0 > 9.6 numerically.

A good example of such usage is in for marking your SQL functions safe to use with the new Parallel Query feature in 9.6 and above.

DO $$
 DECLARE ver integer;
 BEGIN
  SELECT current_setting('server_version_num') INTO ver;
  IF (ver >= 90600) THEN
   EXECUTE ‘alter function f7() parallel safe;’;
  END IF;
 END
$$;