Question 362520 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Is it possible to use a wildcard on the left of a LIKE ANY query in postgres?

In the Postgres view I am querying I have an array property representing tags that have been assigned to a record. I want to be able to query that array for a fragment of an entry so that, for example, if the array contained 'abcdef', 'ghijk', 'lmnop' I could query for '%bcd%' and find that record.

The current query looks like this: SELECT * FROM my_view WHERE ('%abc%' ILIKE ANY (tags) but that doesn’t work at all. I can use the full match – WHERE ('abcdef' ILIKE ANY (tags)) but I need to find data on a partial match as well.

At a pinch I could flatten the array into a string field in the view and use a regular ILIKE against that, but it feels a bit redundant when I already have the data there in the array.

Tags:

2

Answers


  1. 0

    using the unnest() function you can unnest the array into individual elements and then perform the ILIKE comparison

    SELECT * 
FROM my_view 
WHERE EXISTS (
    SELECT 1 
    FROM unnest(tags) AS tag 
    WHERE tag ILIKE '%abc%'
);

    The query will return all records from my_view where at least one tag in the tags array matches the pattern '%abc%'. It effectively achieves the partial match functionality you’re looking for without needing to flatten the array into a string field.

    Login or Signup to reply.
  2. 0

    unnest(tags) function expands the array tags into a set of rows, each containing one tag.

    
SELECT *
FROM my_view
WHERE EXISTS (
    SELECT 1
    FROM unnest(tags) AS tag
    WHERE tag ILIKE '%abc%'
);

    The WHERE clause inside the subquery checks if any of the tags in the expanded set match the pattern %abc% using the ILIKE operator.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search