current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 0 down vote favorite

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 ' > '

share|improve this question
    
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 Sep 23 '14 at 18:53
    
Yes, exactly that. – theartofbeing Sep 23 '14 at 19:07
    
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 Sep 23 '14 at 20:09
    
Thank you! Postgres 9.3.4 If I don't unnest the results will bunch together everything in the categories array. – theartofbeing Sep 23 '14 at 20:12
    
So do you want the result unnested or not? – Erwin Brandstetter Sep 23 '14 at 20:17

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Bingo! Used the first one but unnested categories in the select. Worked like a charm. – theartofbeing Sep 23 '14 at 20:50
up vote 0 down vote

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
share|improve this answer
    
getting error "function length(character varying[]) does not exist" – theartofbeing Sep 23 '14 at 18:53
    
Can you select unnest(categories) into a temporary table? – Myles Baker Sep 23 '14 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.