Hi…
We’ve been using the postcode extension from PGXN for a number of years and it has not caused any problems until now.
The original developer is no longer around, but a couple of minor changes have kept it operating since version 9.
There is an operator (%) and underlying ‘C’ function (postcode_eq_partial) that provides a containment test
e.g. ‘NW10 5AQ’ % ‘NW10’ is true , ‘L17 3PB’ % ‘NW10’ is false etc.
If we run a query against a table with a postcode field but no index using the % operator we get the correct result.
If we run the same query against the table after adding a tree index on the postcode result we get few fewer results.
If we drop and reindex we get a different number of results.
The query is SELECT * FROM XXX where postcode % ’NW10’
To create a sample table — create table XXX ( udprn bigint, postcode postcode )
To Index it CREATE INDEX on XXX(postcode)
The underlying representation of the postcode is a 32 bit integer, so not especially esoteric.
Although the postcode package is probably not especially significant in the scheme of things , the behaviour of indexed versus non-indexed queries is worrisome.
I’ve had to make a couple of minor changes to the postcode package as the Postgres versions have changed, i.e. define TRUE and FALSE in the c header files and change the location of the include files when Postgres 16 arrived, but nothing else.
I’ve attached a sample data file to populate a table that exhibits the behaviour, and the tweaked version of the Postcode package.
Regards, John Burns
Version : PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
—
john@impactdatametrics.com
