Indexing an array of two separate columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux