Select from array in postgresql to get all possible results

Question

im using postgresql 11.2 on a docker container and im trying to get data on this column that containes arrays "event_type" :

   event_type
-----------------
 {ERROR}
 {INFO}
 {ERROR,VERBOSE}
 {ERROR,VERBOSE}
 {INFO,NOTIFY}

event_type column schema :

event_type | text[]

Selecting all rows which contained 'ERROR': (works fine)

 SELECT event_type FROM events WHERE event_type @> ARRAY['ERROR'];
   event_type
-----------------
 {ERROR}
 {ERROR,VERBOSE}
 {ERROR,VERBOSE}
(3 rows)

If i want to get all rows which contained 'ERROR' AND \ OR ' NOTIFY' :

 SELECT event_type FROM events WHERE event_type @> ARRAY['ERROR', 'NOTIFY'];
 event_type
------------
(0 rows)

The desired response would be :

   event_type
-----------------
 {ERROR}
 {ERROR,VERBOSE}
 {ERROR,VERBOSE}
 {INFO,NOTIFY}

Oto Shavadze · Accepted Answer · 2019-06-17 13:59:06Z

3

You can use overlap operator &&

SELECT event_type FROM events WHERE event_type && ARRAY['ERROR', 'NOTIFY'];

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

answered Jun 17, 2019 at 13:59

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

Collectives™ on Stack Overflow

Select from array in postgresql to get all possible results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related