PostgreSQL - Difference in results between LIKE and '=' when using citext

Question

On my DB, when querying

select my_field from my_table where my_field = 'ABC-123a'

it yields 0 results

but when querying

select my_field from my_table where my_field like 'ABC-123a'

it yields one result: ABC-123a

Am on a PostrgreSQL DB, and the field is a citext.

EDIT : the encoding is UTF8

I did some digging and found that LIKE compares character by character, while = compares whole strings.

In that case, I do not understand why using LIKE without any wildcard '%' would lead to these results. As I understand it now, it means that all characters are equals, but the whole string isn't, and I don't get how that is possible?

I do not believe I have trailing spaces either (but I think that doesn't apply for Postgre anyway afaik)

I tried a bunch of queries to debug with ILIKE / TRIM / LENGTH to compare. I looked for special unicode characters / blank spaces etc

But I still haven't found an explanation as to why the = doesn't return anything.

Answer 1

Was fixed by running a reindex index

The LIKE doesn't use the index even without wildcards which explains the differences in results between using = and LIKE

The execution plans were not the same as one was using the - faulty (somehow) - index and the other was not