SQL query with array type in Postgresql

Asked

Viewed 1,552 times

4

How to filter this field of type array in Postgresql?

I have a table X with a field Y of the kind character varying[] with two records:

{'meeting','lunch','training','presentation'}  
{'breakfast','consulting', 'meeting'}

In this context how to make a query of the type:

SELECT * FROM X WHERE Y CONTAINS('lunch')
sql array postgresql

  • Try this: SELECT * FROM X WHERE 'lunch' = ANY (Y);

  • You didn’t call me back at all.

  • Tried to SELECT * FROM X WHERE Y @> ARRAY['lunch']::varchar[]?

  • Also not returned. The funny thing is that in the document itself does not show a query of this.

  • 1

    It may be a problem in the data. Pity that the Sqlfiddle does not work right for me to test.

  • I tried Sqlfiddle tb and I couldn’t even create the schema. :-(

  • After many tests I realized something, cadas string was enclosed by a single quotation mark. {'Meeting','lunch','training','Presentation'} . The two solutions of @Cantoni and bigown are correct, the one of Cantoni with one limitation, but correct for the question.

  • Get someone experienced in BD to help you. It’s totally possible to do this query.

  • These two solutions worked: SELECT * FROM X WHERE 'lunch' = ANY (Y) SELECT * FROM X WHERE Y @> ARRAY['lunch']::varchar[]

  • @bigown, Cantoni, you can turn your comments into answers.

  • Written @Andréhenriques. I received no notification of your comment, strange.

Show 6 more comments

2 answers

4


The contains() will not work even, need to use a more suitable function, the any(), as Cantoni said or use the operator that searches in columns of type array:

SELECT * FROM X WHERE Y @> ARRAY['lunch']::varchar[]

I put in the Github for future reference.

Obviously the data needs to be in proper format.

0

One option is to use ANY or SOME (actually, SOME is an alias for ANY).

SELECT * FROM X WHERE 'lunch' = ANY (Y)

  • I tested with the ANY and it worked, but would it be possible to find 2 elements? 'lunch' and 'Meeting', with the @bigown version works to find how many elements.

  • Try otherwise: SELECT * FROM X WHERE Y= ANY(array['lunch','Meeting'])

  • Got no. I think for the one element would be the ANY, already to buy several elements then should use the operator @>

  • Try to make a Sqlfiddle with this model. It is easier to test. Sqlfiddle gives some bugs, but I’ve made it work for Postgres.

Browser other questions tagged sql array postgresql

You are not signed in. Login or sign up in order to post.