I have a user who is trying to match overlapping duplicate phone info but for different customer_ids.
The intended conditional could be expressed:
IF the intersection of the sets
{c.main_phone, c.secondary_phone}
and
{c1.main_phone, c1.secondary_phone}
is not empty
THEN join
EXCEPT where the intersection of the sets =
{'0000000000'}
He wants a join like this:
FROM customers c
INNER JOIN customers c1 on (array[c.main_phone, c.secondary_phone] && array[nullif(c1.main_phone, '0000000000') , nullif(c1.secondary_phone, '0000000000')])
(array[c.main_phone, c.secondary_phone] && array[nullif(c1.main_phone, '0000000000') , nullif(c1.secondary_phone, '0000000000')])
WHERE c.customer_id = 1;
I want to index this part:
array[nullif(c1.main_phone, '0000000000') , nullif(c1.secondary_phone, '0000000000')]
First of all I see I can't create a btree index on an array. And with btree_gin, this index is not being used:
CREATE INDEX ON customers USING gin ((NULLIF(main_phone, '0000000000'::text)), (NULLIF(secondary_phone, '0000000000'::text)));
What am I missing here? Is there a way to support a condition like this?
Thank you!