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

Question

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

Unrelated to your problem, but: Postgres 9.5 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Jan 4, 2023 at 10:10
This is a horrible database design. Do you have a chance to fix this before proceeding? — user330315
– user330315, Commented Jan 4, 2023 at 10:10
@a_horse_with_no_name It's a Legacy Code. parcels will have zips saved in text format. their initial plan was just to display the values. But now they are providing options to search the parcels based on the zips present. — shajin
– shajin, Commented Jan 4, 2023 at 10:16

Edouard · Accepted Answer · 2023-01-04 10:54:13Z

2

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

answered Jan 4, 2023 at 10:54

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

shajin Over a year ago

This one's perfect. It joins the items in the array like a table, and can easily query through the items. I never thought of any other solution than looping the items in the array. Thanks you my saviour, @Edouard

user330315 · Accepted Answer · 2023-01-04 10:50:47Z

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

FatFreddy · Accepted Answer · 2023-01-04 11:24:16Z

1

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

answered Jan 4, 2023 at 11:24

FatFreddy

1,2101 gold badge10 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related