I'm using postgresql 13 and I'm trying to fetch data from a table based on one of its column.

Said table is defined as follow :

create table my_table (
    my_table_id int8 not null, 
    value varchar(255) not null, 
    another_table_id int8 not null, 
    primary key (my_table_id) );

create index my_table__lower_value__idx 
ON my_table USING btree (lower((value)::text));

Now, when I'm running both query :

• first to select a row with a where clause based on a value defined in another table (column my_table_id)
• second to select the same row and the same table based on a value defined in this table (column value).

Second query is not returning any row.

See below :

db > select * from my_table where my_table_id = 1001;  
my_table_id |  value | another_table_id  
------------+--------+-----------------
     1      | value1 |      1001  
(1 row)

db > select * from my_table where lower(value) = lower('value1');  
my_table_id |  value | another_table_id  
------------+--------+-----------------  
(0 rows)

Mind you, if I ran this query with some other values, it works :

db > select * from my_table where my_table_id = 1002;  
my_table_id |  value | another_table_id  
------------+--------+-----------------
     2      | value2 |      1002  
(1 row)

db > select * from my_table where lower(value) = lower('value2');  
my_table_id |  value  | another_table_id  
------------+---------+-----------------  
     2      |  value2 |      1002  
(1 row) 

Why this difference ?

What I've tried so far :

• using select * from my_table where value in (select value from my_table where another_table_id = 1001); does not work
• using lower on each part of equal statement: still not working on first case.
• using LIKE keyword : it works fine in both cases