Postgres check if any of array values is in a list

Question

How can I check if any of an array values in a single row is in my list?
Here is my table. Let's call it ABC

        id        |   page_id   |                                  values                                   
------------------+-------------+-------------------------------------------------------------------------
 1376092679147519 |     xyz     | {6004036173148,6003373173651,6003050657850}
 1375487155874738 |     xyz     | {6003301698460,6003232518610}
 1497527026945449 |     xyz     | {6003654559478,6003197656807}
 1375388575884596 |     xyz     | {6003512053894,6003450241842,6003051414416}
 1319144441504401 |     xyz     | {6004001256506,6003514818642,6003400993421}

My aim is to select those rows, where one of the values appears in the given list ('6004036173148', '6003197656807').

SELECT id, page_id, values from ABC WHERE -SOME CLAUSE- IN ('6004036173148', '6003197656807');

        id        |   page_id   |                                  values                                   
------------------+-------------+-------------------------------------------------------------------------
 1376092679147519 |     xyz     | {6004036173148,6003373173651,6003050657850}
 1497527026945449 |     xyz     | {6003654559478,6003197656807}

Here is the structure of my PosgreSQL table

                             Table "public.ABC"
       Column       |           Type           |       Modifiers        
--------------------+--------------------------+------------------------
 id                 | character varying        | not null
 page_id            | character varying        | not null
 values             | character varying[]      | 
Indexes:
    "ABC_pkey" PRIMARY KEY, btree (id)

Side note: try to not use reserved words like values as column name — Oto Shavadze
– Oto Shavadze, Commented Apr 13, 2017 at 18:13

Oto Shavadze · Accepted Answer · 2017-04-14 07:43:06Z

2

If I correct understood, you need this:

select * from t
where "values" && '{6004036173148,6003197656807}'::character varying[]

EDIT

If you need extract certain values, then you can use unnest function

Note, that if same array contains more than 1 value from searched list, then row will be repeated. Look this example output with id=2 and you can see, about what I'm talking:

with t(id, values) as(
    select 1, '{6004036173147,6003373173651,6003050657840}'::character varying[]
    union all
    select 2, '{6004036173148,6003373173652,6003050657850}'::character varying[]
    union all
    select 3, '{6004036173149,6003373173653,6003050657860}'::character varying[]
)


select tt.* from  
(select t.*, unnest(values) unn from t) tt
inner join (select unnest('{6003373173651,6004036173148,6003373173652}'::character varying[]) v  ) lst
on tt.unn = lst.v

edited Apr 14, 2017 at 7:43

answered Apr 13, 2017 at 18:04

Oto Shavadze

43.3k55 gold badges168 silver badges248 bronze badges

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

8 Comments

Fuzzy Over a year ago

just out of curiosity how would you do this in tsql?

Oto Shavadze Over a year ago

SQL Server have array types at all?

Fuzzy Over a year ago

I don't Think so.

Oto Shavadze Over a year ago

If so, how would we work with array in tsql? or I missed your question?

Fuzzy Over a year ago

I guess you could fake it: CREATE TABLE #temp ( id VARCHAR(20), page_id VARCHAR(10), rValues VARCHAR(100)) INSERT INTO #temp VALUES ('1376092679147519' ,'xyz','{6004036173148,6003373173651,6003050657850}'), ('1375487155874738' ,'xyz','{6003301698460,6003232518610}' ), ('1497527026945449' ,'xyz','{6003654559478,6003197656807}' ), ('1375388575884596' ,'xyz','{6003512053894,6003450241842,6003051414416}'), ('1319144441504401' ,'xyz','{6004001256506,6003514818642,6003400993421}') SELECT * FROM #temp WHERE PATINDEX('%6003514818642%', rValues) > 0

|

Collectives™ on Stack Overflow

Postgres check if any of array values is in a list

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related