I am trying to create an index that has a field containing concatenated results from several other fields in postgresql.
For example I have an address:
BLACKPOOL FYLDE & WYRE SOCIETY FOR THE BLIND, PRINCESS ALEXANDRA HOME FOR THE BLIND, BOSWORTH PLACE, BLACKPOOL, FY4 1SH
The above is made up of 5 fields, sperated by commas in the example. I would like to create a single index field that contains the data from the 5 fields.
I would like my index to contain 3 fields: uprn, AddressText, wkb_geometry AddressText would contain the above example with the 5 fields concatenated into it , the other 2 fields are just regular fields.
I know how to concatenate in a query "||" and that works fine but can't find anywhere that explains how to do the above. The way I have described is how the address data provider (Ordnance Survey) explains it should be done, but they give no example code to create the index.
I could create a new table with the above, but would have to re-create it when the address data is updated.
The actual query I would use to build an address is this, as you can see its more complicated than just 5 fields:
SELECT uprn,
(
CASE WHEN organisation_name IS NOT NULL THEN organisation_name || ', ' ELSE '' END
|| CASE WHEN sao_text IS NOT NULL THEN sao_text || ', ' ELSE '' END
|| CASE WHEN sao_start_number IS NOT NULL THEN sao_start_number::text ELSE '' END
|| CASE WHEN sao_start_suffix IS NOT NULL THEN sao_start_suffix ELSE '' END
|| CASE WHEN sao_end_number IS NOT NULL THEN '-' || sao_end_number ELSE '' END
|| CASE WHEN sao_end_suffix IS NOT NULL THEN sao_end_suffix ELSE '' END
|| CASE WHEN sao_start_number IS NOT NULL THEN ' ' ELSE '' END
|| CASE WHEN pao_text IS NOT NULL THEN pao_text || ', ' ELSE '' END
|| CASE WHEN pao_start_number IS NOT NULL THEN pao_start_number::text ELSE '' END
|| CASE WHEN pao_start_suffix IS NOT NULL THEN pao_start_suffix ELSE '' END
|| CASE WHEN pao_end_number IS NOT NULL THEN '-' || pao_end_number ELSE '' END
|| CASE WHEN pao_end_suffix IS NOT NULL THEN pao_end_suffix ELSE '' END
|| CASE WHEN pao_start_number IS NOT NULL THEN ' ' ELSE '' END
|| CASE WHEN street_description IS NOT NULL THEN street_description || ', ' ELSE '' END
|| CASE WHEN locality IS NOT NULL THEN locality || ', ' ELSE '' END
|| CASE WHEN town_name IS NOT NULL THEN town_name ELSE '' END
|| CASE WHEN administrative_area <> town_name THEN ', ' || administrative_area ELSE '' END
|| CASE WHEN postcode_locator IS NOT NULL THEN ', ' || postcode_locator ELSE '' END
) AS AddressText, wkb_geometry
FROM addressbase.address
LIMIT 1000
Unfortunately I can't just do this:
CREATE INDEX AddressSearchIndex ON addressbase.address (uprn,
(
CASE WHEN organisation_name IS NOT NULL THEN organisation_name || ', ' ELSE '' END
|| CASE WHEN sao_text IS NOT NULL THEN sao_text || ', ' ELSE '' END
|| CASE WHEN sao_start_number IS NOT NULL THEN sao_start_number::text ELSE '' END
|| CASE WHEN sao_start_suffix IS NOT NULL THEN sao_start_suffix ELSE '' END
|| CASE WHEN sao_end_number IS NOT NULL THEN '-' || sao_end_number ELSE '' END
|| CASE WHEN sao_end_suffix IS NOT NULL THEN sao_end_suffix ELSE '' END
|| CASE WHEN sao_start_number IS NOT NULL THEN ' ' ELSE '' END
|| CASE WHEN pao_text IS NOT NULL THEN pao_text || ', ' ELSE '' END
|| CASE WHEN pao_start_number IS NOT NULL THEN pao_start_number::text ELSE '' END
|| CASE WHEN pao_start_suffix IS NOT NULL THEN pao_start_suffix ELSE '' END
|| CASE WHEN pao_end_number IS NOT NULL THEN '-' || pao_end_number ELSE '' END
|| CASE WHEN pao_end_suffix IS NOT NULL THEN pao_end_suffix ELSE '' END
|| CASE WHEN pao_start_number IS NOT NULL THEN ' ' ELSE '' END
|| CASE WHEN street_description IS NOT NULL THEN street_description || ', ' ELSE '' END
|| CASE WHEN locality IS NOT NULL THEN locality || ', ' ELSE '' END
|| CASE WHEN town_name IS NOT NULL THEN town_name ELSE '' END
|| CASE WHEN administrative_area <> town_name THEN ', ' || administrative_area ELSE '' END
|| CASE WHEN postcode_locator IS NOT NULL THEN ', ' || postcode_locator ELSE '' END
) AS AddressText, wkb_geometry);
The example would then allow me to do a search like this:
SELECT UPRN, AddressText, wkb_geometry FROM AddressSearchIndex
WHERE
AddressText LIKE ‘%4%’ AND
AddressText LIKE ‘%HIGH%’ AND
AddressText LIKE ‘%STREET%’ AND
AddressText LIKE ‘%WESTVILLE%’ AND
AddressText LIKE ‘%WV17%’;
CASE WHEN sao_text IS NOT NULL THEN sao_text || ', ' ELSE '' ENDgives the same result ascoalesce(sao_text||', ','')materialized viewfeature, which is added in PostgreSQL 9.3 wiki.postgresql.org/wiki/Materialized_Views postgresql.org/docs/current/static/…