Use of indexes with % operator produces incorrect results

[GianniFiumetta]

The % operator wlll not work correctly if there is an index on a postcode column.

The problem is that the % is essentially a range operator, but it uses the index equality scheme. Up until Postgres 12 the indexes were structured differently. After PG12, a change to the index structure means that the operator is erroneous.

Use of the postcode_eq_partial function doesn't use indexes, so that is OK.

Workaround using indexes is to create an index on the parts of the postcode you want ,
e.g. using CREATE INDEX .ON

( to_char(postcode,) ) and then use WHERE to_char(postcode,) = 'NW10'. .... can be any of 'A' , 'AD' , 'AD S' , 'AD SW' for area, district, sector and walk.