CODEWITHSUNDEEP
sqlarrayspostgresql
Muhamad Iqbal
Muhamad Iqbal

Reputation: 788

Postgresql: Selecting from list of substrings

I'm really beginner in Postgresql and I'm currently facing a bit of issue in retrieving data.

I have table with these structures

url (text)    | tags (varchar)      | content (text)
some.url.com  | {'news', 'local'}   | 'some content a'
anotherweb.com| {'news', 'headline'}| 'some content b'
isit.web.co   | {'local', 'weather'}| 'some content c'

With DDL as in

CREATE TABLE news (
    link text NULL,
    tags varchar[] NULL,
    "content" text NULL)

PS: This DDL is extracted from DBeaver DDL tab of the table. After checking on database schema, the tags field has data type of 'ARRAY'

I'm trying to retrieve data from this table where the tag contains any from list of substrings that I provide eg. in Python we can write this if expression as

if any(subtr in tags for subtr in [list_of_substrings]):
   # do this

I've found a similar one in Postgres, which is IN. But I haven't found anyone using it with substrings. The current solution I can find is using LIKE with regex (for pattern matching with the substring) but it would ended up with long condition statement like

WHERE tags like '%substr1%' OR
      tags like '%substr2%' OR
      ...

Is there an alternative to the current solution I got?

Upvotes: 1

Views: 397

Answers (1)

user330315
user330315

Reputation:

Your tags column is an array of varchar as you want to check each element, you need to unnest the array:

select n.*
from news n
where exists (select *
              from unnest(n.tags) as x(tag)
              where x.tag like '%substr1%'
                 or x.tag like '%substr2%')

Upvotes: 1

Related Questions