Conditionally construct schema depending on database version in PostgreSQL

Question

Assume the following table and custom range type:

create table booking (
     identifier      integer                not null primary key,
     room            uuid                   not null,
     start_time      time without time zone not null,
     end_time        time without time zone not null
);

create type timerange as range (subtype = time);

In PostgreSQL v10, you can do:

alter table booking add constraint overlapping_times
exclude using gist
(
     room with =,
     timerange(start_time, end_time) with &&
);

In PostgreSQL v9.5/v9.6, you have to manually cast the uuid column as gist_btree does not support uuid:

alter table booking add constraint overlapping_times
exclude using gist
(
     (room::text) with =,
     timerange(start_time, end_time) with &&
);

I would like to support v9.5, v9.6 and v10 for my customers. Is there a way to conditionally add the above constraint in the same .sql file, depending on the version of the current database?

If you already have a shell script for installation, you could do some shell-magic and create the correct (sym)link to the corresponding .sql file(s) and include these in a generic .sql master script. The point is that failing to detect the version would fail. (which is probably what you want) — wildplasser
– wildplasser, Commented Nov 5, 2017 at 12:20
@wildplasser Interesting idea. I could have all common SQL scripts in /common and then have a /v9.5, /v9.5, /v10, ... directory. I'll experiment with this! — Franz They
– Franz They, Commented Nov 5, 2017 at 13:04

Vladimir Aleshin · Accepted Answer · 2017-11-05 11:56:46Z

2

You can use dynamic sql

For example:

do
$block$
declare
  l_version text;
begin
  select setting into l_version from pg_settings where name = 'server_version';

  execute 
    format(
      $script$
        alter table booking add constraint overlapping_times
        exclude using gist
        (
          %s with =,
          timerange(start_time, end_time) with &&
        )
      $script$,
      case when (l_version like '9.5.%' or l_version like '9.6.%') then '(room::text)' else 'room' end
    )
  ;
end;
$block$
  language plpgsql;

answered Nov 5, 2017 at 11:56

Vladimir Aleshin

1914 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Franz They Over a year ago

Cool idea, very useful!

wildplasser · Accepted Answer · 2017-11-05 19:17:24Z

2

Here is a proof of concept:

#!/bin/sh

#THE_HOST="192.168.0.104"
THE_HOST="192.168.0.101"

get_version ()
{
psql -t -h ${THE_HOST} -U postgres postgres <<OMG | awk -e '{ print $2; }'
select version();
OMG
}

# ############################################################################
#       main
#
# - Connect to database to retrieve version
# - use the retrieved version to create a symlink "versioned" to
#   one of our subdirs 
# - call an sql script that includes this symlink/some.sql
# symlinking to a non-existing directory will cause a dead link
# (, and the script to fail.)
# ergo: there should be a subdir "verX.Y.Z" for every supported version X.Y.Z
# ############################################################################

pg_version=`get_version`
#echo "version=${pg_version}"

rm versioned
ln -fs "ver${pg_version}" versioned

if [ -f versioned/alter.sql ]; then
        echo created link versioned to "ver${pg_version}"
else
 echo "version ${pg_version} not supported today..."
 echo "Failed!"
 exit 1
fi

psql -t -h ${THE_HOST} -U postgres postgres <<LETS_GO

\i common/create.sql

\i versioned/alter.sql

\echo done!
LETS_GO


#eof

answered Nov 5, 2017 at 19:17

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

1 Comment

Franz They Over a year ago

I am going to experiment with this this week, thanks!

Collectives™ on Stack Overflow

Conditionally construct schema depending on database version in PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related