PostgreSQL: &&

Colleen Brady

Are there any plans to add the '&&' array 'overlap' operator?

Looking for a way to tell if any of the elements of array1 exist in array2 without doing joins.

Thank you!

Sean Montgomery

Hey ,

This is definitely one of the more fringe postgres features - but - still important. I feel like this particular example is of a bad design by postgres. The concept of overlap is definitely important, but overloading the && symbol seems strange to me.

That being said, we can definitely add this and without the confusing syntax. Do you have a particular timeframe on when this particular feature would be needed?

Colleen Brady

 - Thank you for the brainstorming and suggestions during Office Hours.

Sean Montgomery

Hi ,

We now have support for a "contains" operator. It is available in the list of dropdowns.

However, I'm now re-reading your original post and realizing that you were asking about the overlap operator which means if there are any entries from one array to another. The contains operator checks if all entries from one array are in another. Hopefully that works for you. Otherwise, we will need a bit more time for this specific use case.

Colleen Brady

Thanks for the update .

'contains' operator will be useful in the future. 

In the short term, was looking for the 'overlap' operator.

Colleen Brady

Hello! 

Looking to see if any updates on an 'overlap' operator.

Sean Montgomery

 monday... this was more difficult than expected. That operator is only value with arrays but internally we use jsonb structures, which has similar operators except for that specific one.

All good though - we got it working and will be in monday's release.

Sean Montgomery

this is now live - the overlap option will be in the operator select box between the left and right operand.

Colleen Brady

Thank you very much .  

Colleen Brady

 - Started working with the new functionality this evening. 

Is it designed to support table references? Or -- is it just for simple arrays?

When using the overlap operator on an array of table references, seeing the following:

{
    "message": "Schema does not exist: tableref"
}

Thanks for clarifying.

Sean Montgomery

looks like an oversight. we were testing with normal integers... should be able to fix that tomorrow.

Colleen Brady

Thank you!

Sean Montgomery

 this is fixed.

Colleen Brady

Thank you . 

Colleen Brady

 - Starting to work with functionality from the recent release. 😊

Thank you!

https://www.loom.com/share/8fcdeef69b3640ae9a2a3c313bcbb8db

Sean Montgomery

So cool! Thanks for being patient. This was long overdue.