Where array does not contain value Postgres

Question

I'm using postgres to pull some data. I have an array (categories) and I want to exclude results that contain ' > '

select title, short_url, unnest(categories) as cats, winning_offer_amount
from auctions
where ended_at is not null
and '% > %' NOT IN cats
group by title, short_url, cats, winning_offer_amount

I realize my syntax is completely wrong but trying to give an idea of what I'm looking to write. A result might be:

Women's > Shoes
Women's
Men's > Shoes
Men's

I would want to exclude results with the ' > '

You have a text[] called categories that contains things like those four strings and you want to filter out the array entries that contain '>'? So you'd end up with an array like array['Women''s', 'Men''s']? — mu is too short
– mu is too short, Commented Sep 23, 2014 at 18:53
As always, your version of Postgres, please? And do you really want to unnest the array (so you get 1 row for each element), or is this just your attempt to test? — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 23, 2014 at 20:09
Thank you! Postgres 9.3.4 If I don't unnest the results will bunch together everything in the categories array. — hummmingbear
– hummmingbear, Commented Sep 23, 2014 at 20:12

Erwin Brandstetter · Accepted Answer · 2015-08-31 22:05:38Z

4

A simple, "brute-force" method would be to cast the array to text and check:

SELECT title, short_url, categories, winning_offer_amount
FROM   auctions
WHERE  ended_at IS NOT NULL
AND    categories::text NOT LIKE '% > %';  -- including blanks?

A clean and elegant solution with unnest() in a NOT EXISTS semi-join:

SELECT title, short_url, categories, winning_offer_amount
FROM   auctions a
WHERE  ended_at IS NOT NULL
AND    NOT EXISTS (
   SELECT 1
   FROM   unnest(a.categories) AS cat
   WHERE  cat LIKE '% > %'
   );

SQL Fiddle.

edited Aug 31, 2015 at 22:05

answered Sep 23, 2014 at 20:08

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

hummmingbear Over a year ago

Bingo! Used the first one but unnested categories in the select. Worked like a charm.

Myles Baker · Accepted Answer · 2014-09-23 18:20:31Z

0

Count the number of times the '>' character appears in cats and only include the record if the count is equal to zero.

So, something like this (check for exact syntax):

select title, short_url, unnest(categories) as cats, winning_offer_amount
from auctions
where ended_at is not null
and (length(cats) - length(replace(cats, '>', '')))=0 
group by title, short_url, cats, winning_offer_amount

answered Sep 23, 2014 at 18:20

Myles Baker

3,7802 gold badges22 silver badges25 bronze badges

2 Comments

hummmingbear Over a year ago

getting error "function length(character varying[]) does not exist"

Myles Baker Over a year ago

Can you select unnest(categories) into a temporary table?

Collectives™ on Stack Overflow

Where array does not contain value Postgres

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related