SELECTing from PostgreSQL tables, excluding an array item

Question

I have a table with a column containing an array (lets call it tags for now).

How can I SELECT from that table all rows where the array column DOES NOT contain a certain tag?

Neil Middleton · Accepted Answer · 2013-05-28 17:19:13Z

2

You can try something like:

SELECT *
FROM your_table
WHERE 'certain tag' != ALL (tags);

Neil Middleton

22.3k19 gold badges84 silver badges137 bronze badges

answered May 28, 2013 at 17:17

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CraigKerstiens · Accepted Answer · 2013-05-28 17:11:08Z

1

One way to do this would be getting all ids where that tag is contained, then you can exclude those with a subquery:

SELECT *
FROM foo
WHERE id NOT IN (
  SELECT id
  FROM foo
  WHERE tags @> ['specific-tag-here', 'other-tag-here']);

answered May 28, 2013 at 17:11

CraigKerstiens

5,9891 gold badge27 silver badges29 bronze badges

Maybe WHERE not ( tags @> 'specific-tag-here')? Much simpler and faster