Checking if array is contained in another array in PostgreSQL

Question

There is an array[10,20] and I want to know if it is a subset of array[20,30,10] or not. Is there a postgresql function or operation to determine if one array contains another?

Desired outcomes:

[10,20] and [30,20,10] - true
[10,20] and [10,30]    - false
[10,20] and [20,10]    - true
[10,20] and [10,20]    - true

'An array in PostgreSQL'?? Is this part of some code block? If not, what data structures are we talking about? — Jan Doggen
– Jan Doggen, Commented Feb 28, 2014 at 10:50
@JanDoggen Arrays exist in PostgreSQL at least since version 7, first released in 2001. So I didn't get your question, or the reason why this question was closed. — Rodrigo
– Rodrigo, Commented Mar 4, 2021 at 19:42
@Rodrigo The reason why this question was closed is clearly written in the blue box at the top — Jan Doggen
– Jan Doggen, Commented Mar 4, 2021 at 19:54
@JanDoggen Yes, I've seen it and disagree. Many questions don't show half of this, and they're still open, and sometimes with hundreds, if not thousands, of upvotes. If one understands what arrays are, the question is perfectly clear. And I also didn't understand your question. "What data structures?" Arrays, of course! — Rodrigo
– Rodrigo, Commented Mar 4, 2021 at 20:14
@JanDoggen I also do not understand why this was closed. Could you explain further? Your response "The reason why this question was closed is clearly written in the blue box at the top" is very unhelpful and not descriptive to people less experienced on the site. This question and the accepted answer below was extremely helpful to me and I assume the 7k other viewers this question has had since 2014. Please reconsider reopening the question for new updated answers in the future. Please explain why you think it is off topic and should remain closed if you disagree. — ChrisM
– ChrisM, Commented Oct 6, 2021 at 18:10

Ihor Romanchenko · Accepted Answer · 2014-01-04 18:16:06Z

45

Like this,perhaps:

SELECT ARRAY[10,20]  <@ ARRAY[30,20,10];

http://www.postgresql.org/docs/current/static/functions-array.html

edited Jan 4, 2014 at 18:16

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

answered Jan 4, 2014 at 16:37

Michael Krelin - hacker

144k25 gold badges200 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vaibhav Jain Over a year ago

Worked like charm.

GnojnoW Over a year ago

postgresql.org/docs/current/functions-array.html explains other ones too but <@ was exactly what I needed!

Collectives™ on Stack Overflow

Checking if array is contained in another array in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related