Where in integer field [] Postgresql

Question

Where in integer field [] Postgresql

Navigation

#1 by (4 votes)
#2 by (1 votes)

2

I'm creating a select where I need to check on 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 Sectors column is integer [] (array), how can I make a select request to check if there is a value in another column.

I have tried to do this as follows:

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

Thanks in advance for the help.

php postgresql

asked by anonymous 28.07.2016 / 14:39

2 answers

1

I know two ways to do it:

1st Form:

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

2nd Form:

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

28.07.2016 / 14:59

How to use REST to validate the receipt of XML via POST Windows CMD - Setar String with Accentuation

score 4 · Accepted Answer

Array

At 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 it does not help if the array size is unknown. An alternative is to use the ANY method.

Example

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

In addition, you can find rows where the array has all values equal to 10000.

Example

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

JSON

PostgreSQL version 9.5 is available for json value search.

Documentation

You could do something like this:

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