Postgresql Where All in Array

Postgresql Select rows where column = array

SELECT  *
FROM    table
WHERE   some_id = ANY(ARRAY[1, 2])

or ANSI-compatible:

SELECT  *
FROM    table
WHERE   some_id IN (1, 2)

The ANY syntax is preferred because the array as a whole can be passed in a bound variable:

SELECT  *
FROM    table
WHERE   some_id = ANY(?::INT[])

You would need to pass a string representation of the array: {1,2}

Postgres Match All array values to same column with and condition ---updated

You need to aggregate all event_ids for a single ID:

select id
from table_a
group by id
having array_agg(event_id) @> array[101,103];

The @> is the contains operator so it checks if the array of all event_ids contains the array with those two IDs.

That will return any id that has at least the two event 101 and 103 (which is what you asked for in your question).

If you would like to find those IDs that have exactly those two event_ids (which your sample data does not contain) you could use:

select id
from table_a
group by id
having array_agg(distinct event_id order by event_id) = array[101,103];

Note that the order of the elements in the array matters for the = operator (unlike the "contains" @> operator)

How to use PostgreSQL array in WHERE IN clause?

Don't use IN use ANY, this also removes the need to unnest

where f.id = any (p_ids)

Select all rows where array contains values from a given list in Postgres

You can use the array overlap operator:

SELECT *
FROM table 
WHERE ARRAY[111, 222] && columnarray;

Check if value exists in Postgres array

Simpler with the ANY construct:

SELECT value_variable = ANY ('{1,2,3}'::int[])

The right operand of ANY (between parentheses) can either be a set (result of a subquery, for instance) or an array. There are several ways to use it:

• SQLAlchemy: how to filter on PgArray column types?
• IN vs ANY operator in PostgreSQL

Important difference: Array operators (<@, @>, && et al.) expect array types as operands and support GIN or GiST indices in the standard distribution of PostgreSQL, while the ANY construct expects an element type as left operand and does not support these indices. Example:

• Index for finding an element in a JSON array

None of this works for NULL elements. To test for NULL:

• Check if NULL exists in Postgres array

Postgres: check if array field contains value?

This should work:

select * from mytable where 'Journal'=ANY(pub_types);

i.e. the syntax is <value> = ANY ( <array> ). Also notice that string literals in postresql are written with single quotes.

How to use array in a sql join and check if all of the array elements satisfy a condition?

You can use simple EXISTS predicate testing contacts table with activity.contacts array:

create table activity (
  id int primary key,
  contacts int[]
)

create table contacts (
  id int primary key,
  name varchar(10),
  deleted boolean
)

insert into activity
  select 16 as id, '{1,2,3}'::int[] as contacts union all
  select 15, null union all      
  select 5, '{4}' union all
  select 6, '{6, 5}'

insert into contacts
  select 1 as id, 'q' as name, false as deleted union all
  select 2, 'w', false union all
  select 3, 'e', true union all
  select 4, 'r', false union all
  select 5, 't', true union all
  select 6, 'y', true

delete
from activity a
where not exists (
  select null
  from contacts c
  where not(c.deleted)
    and c.id = any(a.contacts)
)

2 rows affected

db<>fiddle here

PostgreSQL - Get rows that match exactly an array

You can use group by and having:

SELECT i.*
FROM items i JOIN
     items_colors ic
     ON ic.item_id = i.id JOIN
     colors c
     ON c.id = ic.color_id
GROUP BY i.id
HAVING COUNT(*) FILTER (WHERE c.name = ANY (ARRAY['blue', 'red'] ) ) = COUNT(*) AND
       COUNT(*) = CARDINALITY(ARRAY['blue', 'red']);

This assumes that neither the data nor the array have duplicates. It can be tweaked to handle that, but it seems unlikely for this type of problem.

---

Related Topics