Where in integer field[] Postgresql

Asked

Viewed 411 times

2

I am creating a select where I need to check in one of the columns that is of type integer[] if it has one of the values passed in the condition.

for example:

idTarefa | Setores
1        | {1,3,4}
2        | {2}

Knowing that the column Sectors is integer[] (array), how can I make a select asking it to check if in this column there is the value of another array.

I’ve tried to do it this way:

select * from tarefas WHERE IN(ARRAY[2,4])

Thanks for your help.

php postgresql

2 answers

4


Array

In the documentation we have :

To search for a value in an array, each value needs to be checked. This can be done manually if you know the size of the array.

Example

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

However, this will be tedious if you have a very large array, and does not help if the array size is unknown. An alternative is to use the method ANY.

Example

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

Also, you can find lines where the matrix has all values equal to 10000.

Example

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

JSON

As of version 9.5 of Postgresql this available json value search.

Documentation

You could do something like this :

SELECT * FROM tabela WHERE column_name::jsonb ? array['b', 'c'];

  • Really @Guilherme this way SELECT * FROM tb_tarefas WHERE 2 = ANY (Setores); He returns the tasks to me, but if I have more than one number to check how I proceed? Because I have to pass an array of Sectors to check if in that task there are any of these sectors.

  • 1

    William thank you for your help, worked for me the examples you gave me, just could not do the last. Now I’ll just sort out how I do it in an easy way to check if the values within one array exist within another. Again Thank you!

1

I know two ways to do:

1st form:

SELECT  *
FROM    tarefas 
WHERE   Setores = ANY(ARRAY[2, 4]);

Second form:

SELECT  *
FROM    tarefas 
WHERE   Setores IN (2, 4);

  • Using any of the options it returns me this error: HINT: No Operator Matches the Given name and argument type(s). You Might need to add Explicit type Casts. The first stops at the "=" (equal) sign before the ANY and following him to IN

  • You are passing some variable in php or putting the direct number ?

  • Right now, I’m just testing directly on pgAdmin, passing select. Doing like this: SELECT * FROM tb_tarefas WHERE cod_subsetores @> ARRAY[2,4]; it even returns something to me, but only the task that has in the array the number 2 and the number 4, the other task that has only the number 2 it does not return me.

  • I can’t find anything in the documentation about the mentioned operator @> http://pgdocptbr.sourceforge.net/pg80/functions-comparison.html

  • You can find this information about this operator here: https://www.postgresql.org/docs/9.1/static/functions-array.html

Browser other questions tagged php postgresql

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