How do I perform a LIKE query on a PostgreSQL primary id column?

Question

If I have a number (such as 88) and I want to perform a LIKE query in Rails on a primary ID column to return all records that contain that number at the end of the ID (IE: 88, 288, etc.), how would I do that? Here's the code to generate the result, which works fine in SQLLite:

@item = Item.where("id like ?", "88").all

In PostgreSQL, I'm running into this error:

PG::Error: ERROR:  operator does not exist: integer ~~ unknown

How do I do this? I've tried converting the number to a string, but that doesn't seem to work either.

Answer 1

Simple case

LIKE is for string/text types. Since your primary key is an integer, you should use a mathematical operation instead.

Use modulo to get the remainder of the id value, when divided by 100.

Item.where("id % 100 = 88")

This will return Item records whose id column ends with 88

1288
1488
1238872388
862388

etc...

Match against arbitrary set of final two digits

If you are going to do this dynamically (e.g. match against an arbitrary set of two digits, but you know it will always be two digits), you could do something like:

Item.where(["id % 100 = ?", last_two_digits)

Match against any set or number of final digits

If you wanted to match an arbitrary number of digits, so long as they were always the final digits (as opposed to digits appearing elsewhere in the id field), you could add a custom method on your model. Something like:

class Item < ActiveRecord

  ...

  def find_by_final_digits(num_digits, digit_pattern)
    # Where 'num_digits' is the number of final digits to match
    # and `digit_pattern` is the set of final digits you're looking fo

    Item.where(["id % ? = ?", 10**num_digits, digit_pattern])
  end

  ...

end

Using this method, you could find id values ending in 88, with:

Item.find_by_final_digits(2, 88)

Match against a range of final digits, of any length

Let's say you wanted to find all id values that end with digits between 09 and 12, for whatever reason. Maybe they represent some special range of codes you're looking up. To do this you could do another custom method to use Postgres' BETWEEN to find on a range.

def find_by_final_digit_range(num_digits, start_of_range, end_of_range)
  Item.where(["id % ? BETWEEN ? AND ?", 10**num_digits, start_of_range, end_of_range)
end

...and could be called using:

Item.find_by_final_digit_range(2, 9, 12)

...of course, this is all just a little crazy, and probably overkill.