How to fetch all rows where an array contains any of the fields array elements

I have a table that has a column video_ids, it is of a bigint[] type. I would like to find all the rows that have any of the elements from the array passed in a select statement. So, if I have a row that has a video_ids field that looks like this:

{9529387, 9548200, 9579636}

I would like to fetch it if I pass an array that has any of this video_ids. I thought I would do that with any, but I am not sure how to do this in SQL, I have tried with this:

select id, finished, failed, video_ids, invoiced_video_ids, failed_video_ids
from video_order_execution
where order_ids = any(
    '{9548200, 11934626, 9579636, 11936321, 11509698, 11552728, 11592106, 11643565, 11707543, 11810386, 11846268}'
        ::bigint[]);

I get an error if I do that:

ERROR: operator does not exist: bigint[] = bigint Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

How can I make such a statement that would do the job for what I need?

Answer

Use the operator && which returns true if the 2 operands have any common items:

select id, finished, failed, video_ids, invoiced_video_ids, failed_video_ids
from video_order_execution
where order_ids &&
  '{9548200, 11934626, 9579636, 11936321, 11509698, 11552728, 11592106, 11643565, 11707543, 11810386, 11846268}'::bigint[];

Advertisement

Answer