DBI, Postgresql and binding values for intervals

January 31, 2013

I have spent most of the day scratching my head over this one. I have a PostgreSQL database which has an “events” table, within which there is a “logdate” column (which is a simple date field). I need to be able to run an SQL query which gives me all events older than a certain length of time (e.g. an interval of so many days, weeks or months). The standard way to do this is to prepare a query with a “placeholder” into which a string supplied by the user can be safely inserted.

I am using Perl for this project and initially I was trying this with the excellent DBIx::Class module (which uses the SQL::Abstract module internally). The weirdness of the problem had me convinced there was a bug somewhere so I spent a while upgrading everything to the latest versions in the hope that the issue would disappear. When this did not help I reduced the problem to the simplest version possible using the standard DBI interface, like below:

use DBI

my $dbh = DBI->connect("dbi:Pg:dbname=buzzsaw;");
$sth = $dbh->prepare(
     "select count(*) from event
         where ( logdate < current_date - interval ?)");
$sth->execute("26 weeks")'

but this still produces an error message like this:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: ...(*) from event where ( logdate < current_date - interval $1)
                                                                    ^ at -e line 1.

At this point it became clear that this is a “feature” or, at the very least, the lack of a feature rather than a bug. A bit of hunting around revealed this ancient Debian bug report from September 2006. Thankfully the bug report does contain a work-around which is to use the placeholder as a string and then cast it to an interval like this:

use DBI

my $dbh = DBI->connect("dbi:Pg:dbname=buzzsaw;");
$sth = $dbh->prepare(
     "select count(*) from event
         where ( logdate < current_date - ?::interval)");
$sth->execute("26 weeks")'

Given the grief this caused me I thought it worth committing to my blog in the hope that the next person to hit this issue will find the solution quicker than I did. This is definitely a lesson in why the best approach is to reduce a problem to its simplest form rather than just assuming something has a bug.