Re: current_schema will not use an text index ?

Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> writes:
> customer_schema text, --here is the problem, a text column.

> Until version 11 my select was using that index correctly. Then I´ve
> upgraded to 14.1, then ...

> explain analyze select customer_schema, pk from audit where customer_schema
> = current_schema and table_name =

"current_schema" is nowadays considered to have C collation, which is
appropriate for comparisons to columns in the system catalogs. But that
causes your "customer_schema = current_schema" comparison to resolve as
having C input collation, which doesn't match the collation of your index
on customer_schema. You could either change the query to look like

where customer_schema = current_schema collate "default" and ...

or else change the table so that customer_schema has "C" collation.

The reason the behavior changed is that we're less cavalier about
the collation of type "name" than we used to be.

regards, tom lane