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 $$;
Leave a Reply
Want to join the discussion?Feel free to contribute!