0

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
L. Malegue
  • 21
  • 4
  • Some trailing whitespace maybe? What does `where lower(trim(value)) = lower('value1');` return? –  Mar 24 '22 at 14:53
  • 1
    Did this work in the past? Have you updated/upgraded the OS recently? Have you tried reindexing the `index my_table__lower_value__idx` index? – Adrian Klaver Mar 24 '22 at 14:58
  • @AdrianKlaver, an update was performed on the database itself (from postgresql 9.6 to 13). I didn't try to reindex the index, yet. I might give it a try soon. For your information, I've managed to "fix" it by updated value to uppercase : `update my_table set value = upper(value) where my_table_id = 1001`. Setting it back to lower still work. – L. Malegue Mar 25 '22 at 08:46
  • @a_horse_with_no_name , it works ! But it does not seem to use the index. – L. Malegue Mar 25 '22 at 09:02

1 Answers1

0

Reindex the table resolved the issue for me.

REINDEX TABLE <table name>;

But not sure why the index causes this kind of issue.

Luc Thi
  • 21
  • 1
  • 1