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 number means. In this example, the extension was updated from Version 1.0 to 1.1 because we explicitly provided a script for that specific conversion. PostgreSQL did not deduce...