PostgreSQL like not returning matching instances

Question

How does the PostrgeSQL like function work? I'm using token inputs to limit input from user with only existing values.

I have the following values in the DB:

  `Yellow, White, Orange...`

My Code

 @colors = Color.where("name like ?", "%#{params[:q]}%")

If I type in w for example White is not proposed. I have to type in second letter to see White proposed. Because Db values all start by Capital letter I suspect a difference with SQLite.

I found this post which mentions ILIKE but was wondering if there is some common code that work both with Postgres and SQLite.

Storing comma separated values is usually the sign of a questionable database design. — user330315
– user330315, Commented Nov 21, 2011 at 23:01

Erwin Brandstetter · Accepted Answer · 2011-11-21 21:20:43Z

2

The SQLite LIKE operator is case insensitive per default.
In PostgreSQL ILIKE is the case insensitive version of LIKE. There are also operators:

~~   .. LIKE
~~*  .. ILIKE
!~~  .. NOT LIKE
!~~* .. NOT ILIKE

These three expressions are all effectively the same in PostgreSQL:

name ilike '%w%'
name ~~*   '%w%'
lower(name) like lower('%w%')

The last line mostly works in both SQLite and PostgreSQL. I added links to the respective manual pages.

A limitation applies: SQLite only understands lower / upper case of ASCII characters, while PostgreSQL understands other UTF-8 characters, too.

edited Nov 21, 2011 at 21:20

answered Nov 21, 2011 at 20:58

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Gaelle Over a year ago

Thanks for the full Db explanation.

mu is too short · Accepted Answer · 2011-11-21 21:07:15Z

0

The case-sensitivity of LIKE depends on the database you use. Some databases ignore case when using LIKE, some don't, some look at various configuration options. One way around this is to normalize the case yourself by converting to upper or lower case:

@colors = Color.where("lower(name) like ?", "%#{params[:q].downcase}%")

answered Nov 21, 2011 at 21:07

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Collectives™ on Stack Overflow

PostgreSQL like not returning matching instances

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related