Last Updated: February 25, 2016
· jwo

Postgres, Rails, and Regex's

If you have a column that contains both string and integer values in Postgres, you can limit results to just integer values by using a regular expression:

select * from responses where value ~ '^\d+$'

Pretty cool stuff... In Rails, it's a little tricky to use, but the easiest way I found is to move into a scope

class Response < AR::Base
  scope :integer_values, -> { where("value ~ ?", '^\d+$') }

Which you would then use thusly:

Response.integer_values.where("value > 5")

1 Response
Add your response

you can also use a partial index in postres to optimize this query:

create index concurrently integer_responses on responses ((value ~ '^\d+$'));

and don't forget the opposite case as well:

create index concurrently string_responses on responses ((value !~ '^\d+$'));

Then when you execute the query/scope you will still benefit from an index.

over 1 year ago ·