1

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%’;
16
  • 1
    BTW CASE WHEN sao_text IS NOT NULL THEN sao_text || ', ' ELSE '' END gives the same result as coalesce(sao_text||', ','') Commented Sep 30, 2014 at 13:39
  • I don't think the example search is all that great anyway. My internet searches appear to throw up lots of examples of Full Text Searches using TSVECTOR but I'm thrown off this due to official examples from the data provider saying differently. The examples are generic SQL not specific for postgresql though. Any help greatly appreciated. Commented Sep 30, 2014 at 13:39
  • I think you are searching for the materialized view feature, which is added in PostgreSQL 9.3 wiki.postgresql.org/wiki/Materialized_Views postgresql.org/docs/current/static/… Commented Sep 30, 2014 at 13:41
  • @pozs there is no need in MVIEW for this case. A simple functional index or a column+trigger will do the job. Commented Sep 30, 2014 at 13:42
  • @IgorRomanchenko thanks, coalesce is a bit easier to read. Commented Sep 30, 2014 at 13:44

1 Answer 1

1

Use the information you can find in the internet on Postgres full text search or Postgres official manual: http://www.postgresql.org/docs/current/static/textsearch.html

As I see you will need:

1) An GIN or GIST index on your expression (see full text search examples).

2) Create a simple VIEW (not MVIEW) on the base table + your expression.

3) Use full text search queries to your VIEW.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.