Using PostgreSQL unnest to Filter Travel Island Destinations from a Comma‑Separated Field

The vacation TTS system stores popular island destinations (e.g., Maldives, Phuket, Bali, Saipan, Sabah, Long Beach) in a column named arrive , but the values are saved as a comma‑separated string, making direct matching difficult.

Many would reach for a regular expression, yet the requirement is simply to check whether any of the listed islands appear in the field, which would lead to cumbersome and hard‑to‑maintain patterns.

The final demand is to avoid manual regex or full‑text indexing and instead find a straightforward solution that can treat the comma‑separated values as individual rows.

PostgreSQL provides a powerful feature that can convert a delimited string into an array, which can then be expanded into separate rows. The function unnest , available since PostgreSQL 9.1, works perfectly for this purpose.

unnest

Example usage:

select * from 
(
  select id, sid, unnest(string_to_array(arrive, ',')) as arrive 
  from pds
) b
where arrive in ('马尔代夫','普吉岛','巴厘岛','塞班','沙巴','长滩');

For more complex reporting, the same technique can be combined with conditional aggregation to pivot island counts into separate columns:

select a.sid, b.sna,
  sum(case when arrive = '马尔代夫' then 1 else 0 end) as 马尔代夫,
  sum(case when arrive = '沙巴' then 1 else 0 end) as 沙巴,
  sum(case when arrive = '普吉岛' then 1 else 0 end) as 普吉岛,
  sum(case when arrive = '巴厘岛' then 1 else 0 end) as 巴厘岛,
  sum(case when arrive = '塞班' then 1 else 0 end) as 塞班,
  sum(case when arrive = '长滩' then 1 else 0 end) as 长滩
from (
  select * from (
    select id, sid, unnest(string_to_array(arrive, ',')) as arrive 
    from pds
  ) a 
  where arrive in ('马尔代夫','普吉岛','巴厘岛','塞班','沙巴','长滩')
) a 
join bsi b on a.sid = b.id 
group by a.sid, b.sna;

Author: He Weiping, senior search and database researcher at Qunar, translator of the first Chinese PostgreSQL manual and the third edition of Programming Perl, with extensive experience in search, distributed systems, and database technologies.