Select with IN operator in array column in Postgres

Question

Good evening, everyone!

I have a column (ids_authors) which is an array of integers in a Postgres table. I am trying to make a select with the IN operator in this column, but it is giving error. I am trying to make the following query:

SELECT 
titulo,
ids_autores
FROM tb_livros 
WHERE ids_autores IN (10,15)

Does anyone know how to make an appointment like this?

Answer 1

Assuming your table is something like:

CREATE TABLE tb_livros
(
  id SERIAL PRIMARY KEY,
  titulo TEXT,
  ids_autores INT[]
);

Containing the following data:

INSERT INTO tb_livros ( titulo, ids_autores ) VALUES ( 'Fooz', ARRAY[5] );
INSERT INTO tb_livros ( titulo, ids_autores ) VALUES ( 'Foo', ARRAY[10] );
INSERT INTO tb_livros ( titulo, ids_autores ) VALUES ( 'Bar', ARRAY[15] );
INSERT INTO tb_livros ( titulo, ids_autores ) VALUES ( 'FooBar', ARRAY[10,15,20] );
INSERT INTO tb_livros ( titulo, ids_autores ) VALUES ( 'Baz', ARRAY[10,20,30] );

Like the field ids_autores if it’s a guy ARRAY, you must use the operator @> to check if your list of ids contains the ids you want to filter.

Solution 1: Retrieves all books with identifiers as authors 10 and 15:

SELECT 
  id,
  titulo,
  ids_autores
FROM
  tb_livros 
WHERE
  ids_autores @> ARRAY[10,15];

Exit:

| id | titulo | ids_autores |
|----|--------|-------------|
|  4 | FooBar |    10,15,20 |

Solution 2: Recovers all books that have authors with identifiers 10 or 15:

SELECT 
  id,
  titulo,
  ids_autores
FROM
  tb_livros 
WHERE
  ids_autores @> ARRAY[10] OR
  ids_autores @> ARRAY[15];

Exit:

| id | titulo | ids_autores |
|----|--------|-------------|
|  2 |    Foo |          10 |
|  3 |    Bar |          15 |
|  4 | FooBar |    10,15,20 |
|  5 |    Baz |    10,20,30 |

See working on SQLFiddle

Answer 2

Use the appropriate array operator, in this case OVERLAPS:

SELECT titulo, ids_autores
    FROM tb_livros 
    WHERE ids_autores && ARRAY[10,15];

If one of the elements, 10 or 15, exists in the array.