Case insensitive Postgres query with array contains

Question

I have records which contain an array of tags like these:

   id   |                     title                     |       tags
--------+-----------------------------------------------+----------------------
 124009 | bridge photo                                  | {bridge,photo,Colors}
 124018 | Zoom 5                                        | {Recorder,zoom}
 123570 | Sint et                                       | {Reiciendis,praesentium}
 119479 | Architecto consectetur                        | {quia}

I'm using the following SQL query to fetch a specific record by tags ('bridge', 'photo', 'Colors'):

SELECT  "listings".* FROM "listings" WHERE (tags @> ARRAY['bridge', 'photo', 'Colors']::varchar[]) ORDER BY "listings"."id" ASC LIMIT $1  [["LIMIT", 1]]

And this returns a first record in this table.

The problem with this is that I have mixed type cases and I would like this to return the same result if I search for: bridge, photo, colors. Essentially I need to make this search case-insensitive but can't find a way to do so with Postgres.

This is the SQL query I've tried which is throwing errors:

SELECT  "listings".* FROM "listings" WHERE (LOWER(tags) @> ARRAY['bridge', 'photo', 'colors']::varchar[]) ORDER BY "listings"."id" ASC LIMIT $1

This is the error:

PG::UndefinedFunction: ERROR: function lower(character varying[]) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

klin · Accepted Answer · 2016-12-01 13:15:06Z

11

You can convert text array elements to lower case in the way like this:

select lower(tags::text)::text[]
from listings;

          lower           
--------------------------
 {bridge,photo,colors}
 {recorder,zoom}
 {reiciendis,praesentium}
 {quia}
(4 rows)

Use this in your query:

SELECT * 
FROM listings
WHERE lower(tags::text)::text[] @> ARRAY['bridge', 'photo', 'colors'] 
ORDER BY id ASC;

   id   |    title     |         tags          
--------+--------------+-----------------------
 124009 | bridge photo | {bridge,photo,Colors}
(1 row)

answered Dec 1, 2016 at 13:15

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

pozs Over a year ago

lower(tags::text)::text[] certainly works in all cases, but still it seems soo dirty

klin Over a year ago

Dirty? It's a matter of taste. Dirty but fast! ;)

Nick Barnes · Accepted Answer · 2016-12-01 13:10:59Z

5

You can't apply LOWER() to an array directly, but you can unpack the array, apply it to each element, and reassemble it when you're done:

... WHERE ARRAY(SELECT LOWER(UNNEST(tags))) @> ARRAY['bridge', 'photo', 'colors']

You could also install the citext (case-insensitive text) module; if you declare listings.tags as type citext[], your query should work as-is.

answered Dec 1, 2016 at 13:10

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

1 Comment

shime Over a year ago

thank you Nick, accepted the other answer because it doesn't require installing additional extensions.

Collectives™ on Stack Overflow

Case insensitive Postgres query with array contains

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related