Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres array overlap operator (&&) in case of empty array

I am running the below query. the (&&) operator is used to filter the result set with any overlaping array elements and is working fine if i have a non empty array but in case of an empty array the result set is empty which can be reasoned out but can an empty array be treated as a universal set in this case maybe something like array && ({*}::int[]) I wanted to know if there is a workaround for something like this.

select  pm.id,
        sum(c.density) as score
from    
    metadata pm,
    unnest(pm.dimen_one_id::int[], pm.dimen_one_density::float[]) as c(id, density)
where   
    c.id = any('{1}'::int[])
group by
    pm.pin_id,
    pm.dimen_one_id,
    pm.dimen_two_id,
    pm.dimen_three_id
having 
    pm.dimen_two_id && '{36,37,38}'::int[] and 
    pm.dimen_three_id && '{23}'::int[]  
order by 
    score desc
limit 
    10
offset  
    0
like image 722
harshitpthk Avatar asked Oct 16 '25 03:10

harshitpthk


1 Answers

select count(*) from table_name where (cardinality(array::bigint[]) = 0) or (col_name && array::bigint[]);

where array is the actual array.

like image 52
Gaurav Jain Avatar answered Oct 18 '25 17:10

Gaurav Jain



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!