PostgreSQL: Where condition using the type of elements in the Array-CodePudding

I have a column 'zips' with type 'text' in the table parcels. User can fill either a single zip code, OR multiple comma separated zips, OR a range of zips separated by a hyphon.
Examples of possible datas are.

'10001'  
'10002,10010,10015'
'10001,"10010-10025"'

I need to match the records with a zipcode '10015'.

eg:

select * 
from parcels 
where "10015" = ANY(string_to_array(parcels.zips, ','))

The Above code is working for the comma separated zips, but I am not sure about how to deal with the ranges.

I am looking for something like

select * 
from parcels 
where (   
  loop though `string_to_array(parcels.zips, ',')` and if iterating    
   variable contains '-', then 'where 10015 BETWEEN 10010 AND 10025'.
   ELSE if zip doesn't contains '-', Then '10015' = '10001(other elements in the array)'
) 
and combine the loop conditions with OR

CodePudding user response：

try this :

SELECT * 
FROM parcels p
CROSS JOIN LATERAL regexp_split_to_table (p.zips, ',') AS z
WHERE CASE
        WHEN strpos (z, '-') > 0
        THEN '10015' BETWEEN split_part (z, '-', 1) AND split_part (z, '-', 2)
        ELSE z = '10015'
      END

CodePudding user response：

You can unnest the elements of the column and use them in an EXIST condition that checks for ranges:

select *
from parcels p
where exists (select *
              from (
                  select split_part(trim(both '"' from z.zip), '-', 1) as from_zip,
                         split_part(trim(both '"' from z.zip), '-', 2) as to_zip
                  from unnest(string_to_array(p.zip_codes, ',')) as z(zip)
              ) x
              where (x.to_zip = '' and x.from_zip = '10015')
                 or (x.to_zip <> '' and '10015' between x.from_zip and coalesce(x.to_zip, '10015'))
              );

I would put this into a function to make that easier:

create function contains_zip(p_codes text, p_zip_code text)
  returns boolean
as
$$
  select exists 
    (select *
     from (
       select split_part(trim(both '"' from z.zip), '-', 1) as from_zip,
              split_part(trim(both '"' from z.zip), '-', 2) as to_zip
       from unnest(string_to_array(p_codes, ',')) as z(zip)
     ) x
     where (x.to_zip = '' and x.from_zip = p_zip_code)
        or (x.to_zip <> '' and p_zip_code between x.from_zip and coalesce(x.to_zip, p_zip_code))
    );
$$
language sql
immutable;

Then it is as easy as:

select *
from parcels p
where contains_zip(p.zip_codes, '10015');

Online example

CodePudding user response：

try to get your data in the format you need them, with some CTEs:

with _data as (
select * from (values(1,'10001'),(2,'10002,10010,10015'), (3,'10001,"10010-10025"')) as _vals (i,x)
),

_data2 as ( 
select 
i,
unnest(string_to_array(x,','))as x
from _data
),

_data3 as (
select
i,
x,
replace(split_part(x,'-',1),'"','') as x1,
replace(split_part(x,'-',2),'"','') as x2
from  _data2
)

select * from _data3
where 
case when x2 = '' then x1::int = 10015 end
or 
case when x2 <> '' then 10015 between(x1::int) and (x2::int) end