current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to query a Postgres Array Column disregarding case and perhaps even disregarding spaces as well.

SELECT "cats".* FROM "cats" WHERE ('CATS - PERSA' = ANY(UPCASE(cat_types))) ORDER BY "cats"."id" ASC LIMIT 1;

But I get this error:

You might need to add explicit type casts.

AS a bonus I would like to also be able to do a regexp where the search ignores spaces in values on the cat_types column.

I am using Ruby on Rails to do this.

cat_type.upcase.delete(' ')
Cats.where("'#{cat_type}' = ANY(cat_types)").first

The query works just using ANY but I want to be able to disregard spaces and upcase the values in cat_types so that it has more chances of matching. Ilike could also be a possibility.

Thanks.

share|improve this question
2  
Have you tried 'CATS - PERSA' ~* ANY(cat_types)? It will do case-insensitive regexp mach. –  Igor Romanchenko Apr 9 at 20:08
    
I had not tried that, did not even know it existed, that worked and it disregarded the case. Thanks.It would be nice to disregard spaces as well. –  rii Apr 9 at 20:21
    
You may want to read this page. It could be usefull to get some time and read the full manual - it has alot of nice things you do not know about. –  Igor Romanchenko Apr 9 at 20:28
    
I don't know if this will work with the ANY clause, but try something like: ANY(cat_types) ~* 'CAT\s*-\s*PERSA' –  Coenwulf Apr 9 at 20:36
    
You report problems with type casts and still keep the actual data types from us? Best provide the table definition (what you get with \d tbl in psql) . –  Erwin Brandstetter Apr 9 at 21:35

1 Answer 1

up vote 0 down vote 
SELECT DISTINCT c.*
FROM   cats c, unnest(c.cat_types) AS cat_type
WHERE  upper(translate(cat_type, ' ', '')) = 'CATS-PERSA'
ORDER  BY id
LIMIT  1;

  • The Postgres function is upper(), not upcase().

  • cat_types seems to be an array, assuming type text[] (info missing). I unnest() to treat array elements individually. This cannot be done with ANY, which is only good for simple comparison.

  • I use an implicit LATERAL JOIN here, requires Postgres 9.3+ (info missing).

  • If multiple array elements match, you get the row multiple times here. Hence the DISTINCT.

More about pattern-matching in Postgres:
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

share|improve this answer

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.