Database Reference
In-Depth Information
Extension versions
The version mechanism for PostgreSQL extensions is simple. Name it whatever you
want and give it whatever alphanumeric version number that suits your fancy. Easy,
eh? Just name the files with this format:
extension--version.sql
If you want to provide an upgrade path from one version of your extension to another,
you would provide the file:
extension--oldversion--newversion.sql
This simple mechanism allows PostgreSQL to update an extension that is already in
place. Gone are the days of painful exporting and re-importing data just to change the
definition of a data type. So, let's go ahead and update our example extension using
the file
postal--1.0--1.1.sql
. This update is as easy as:
ALTER EXTENSION postal UPDATE TO '1.1';
A note of caution: PostgreSQL does not have any concept of what your version num-
ber means. In this example, the extension was updated from Version 1.0 to 1.1 be-
cause we explicitly provided a script for that specific conversion. PostgreSQL did not
deduce that 1.1 follows 1.0. We could have just as easily used the names of fruits
or historical battleships for our version numbers and the result would have been the
same.
PostgreSQL will use multiple update files if necessary to achieve the desired result.
Given the following command:
ALTER EXTENSION postal UPDATE TO '1.4';
PostgreSQL will apply the files
postal--1.1--1.2.sql, postal-
-1.2--1.3.sql
and
postal--1.3--1.4.sql
in the correct order to achieve the
desired version.
