CODEWITHSUNDEEP
arrayspostgresql
shime
shime

Reputation: 9008

Case insensitive Postgres query with array contains

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.

Upvotes: 5

Views: 4582

Answers (2)

klin
klin

Reputation: 121534

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)

Upvotes: 10

Nick Barnes
Nick Barnes

Reputation: 21326

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.

Upvotes: 5

Related Questions