Today I learned how to fix a type casting bug in our Postgresql script with regular expressions. This deserves celebration! Details below.

The Problem

We have a database table that matches data to other tables based on a specific column. The values for the column are provided by the user and stored as strings. Depending on the table being matched, the values can be strings or numbers. If the user submitted bad input for a number match, accidentally including characters in their match column, the query fails.

The code handling the input was exactly the same for every match column type. I didn’t want to add a special case for this. The problem was in the query, so the fix should be in the query.

The Solution

Update: I subscribe to Andy Croll’s Ruby newsletter. He suggests using \A and \Z to match the beginning and ends of a string because ^ and $ will match the ends of a line. If your string has newlines in it, it will be accepted by the regex.

Postgres has regular expression match operators. We can update our query to check that a value is a number.

-- Example query
  FROM list_of_things lot
  WHERE lot.value ~ '^[0-9]+$' AND lot.value::BIGINT = 12002;

The ^ operator matches the regular expression to the start of the string and the $ matches to the end. By combining them with [0-9]+ we check that every character in the string is a digit.