2

I have the following query:

select case when count(*)>0 then true else false end
from tab
where param in ('a','b') and position('T' in listofitem)>0

This checks if 'T' exists in the column listofitem and if it does the count is > 0. Basically it's a search for sub string.

This works well in this private case. However my real case is that I have text[] called sub_array meaning multiple values to check. How can I modify the query to handle the sub_array type? I prefer to have it in a query rather than a function with a LOOP.

What I actualy need is:

select case when count(*)>0 then true else false end
from tab
where param in ('a','b') and position(sub_array in listofitem)>0

This is not working since sub_array is of type Text[]

Improve this question
1
  • try unnesting the array Commented Apr 3, 2017 at 12:56

2 Answers 2

Reset to default
6

Use the unnest() function to expand your array & bool_and() (or bool_or() -- this depends on what you want match: all array elements, or at least one) to aggregate:

select count(*) > 0
from   tab
where  param in ('a','b')
and    (select bool_and(position(u in listofitem) > 0)
        from   unnest(sub_array) u)
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I think you misunderstood. The 'T' is just an example. I need to replace it with the sub_array. If sub_array contains '3a', 'c','h' than I need to search these three strings inside the tab table.
@avi the main idea did not change. use unnest to expand & aggregation to summarize. the actual formulas do not matter that much. (I updated my answer).
Thanks. You have a syntax error near the 0... one brackets too many. However this still doesn't work in all cases. If My rows are ''28713" and "-1,37561,37560" . When sub_array='37588','37560' it doesn't find the 37560. But if sub_array=37561,37560 it works.
@avi yes, that's exactly the case I noted when proposed to use either bool_and or bool_or. If you need only one element in the array to be matched, use bool_or
@avi use where u <> -1 in the sub-select. u is both a table alias & a column alias in my example. if you want different aliases, you could use the from unnest(sub_array) table_alias(column_alias) form.
|
0

A brute force method would be to convert the array to a string:

select (count(*) > 0) as flag
from tab
where param in ('a','b') and
      array_to_string(listofitem, '') like '%T%';

I should note that comparing count(*) is not the most efficient way of doing this. I would suggest instead:

select exists (select 1
               from tab
               where param in ('a','b') and
                     array_to_string(listofitem, '') like '%T%'
              ) as flag;

This stops the logic at the first match, rather than counting all matching rows.

Improve this answer

1 Comment

How do I replace the %T% to variable? I can't write '%sub_array%'. By the way it doesn't stop the logic at the first match. The inner query will return multiple rows that contains 1.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.