Postgresql: Selecting from list of substrings

Question

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?

What data type is tags exactly? varchar or an array, e.g. varchar[]? Please edit your question and add the create table statement (as formatted text, no screen shots please) . — user330315
– user330315, Commented Mar 25, 2021 at 9:08
@a_horse_with_no_name i've added it, tho I'm accessing the database thru DBeaver — Muhamad Iqbal
– Muhamad Iqbal, Commented Mar 25, 2021 at 10:00

user330315 · Accepted Answer · 2021-03-25 10:05:18Z

1

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%')

answered Mar 25, 2021 at 10:05

user330315

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

2 Comments

Muhamad Iqbal Over a year ago

What if i'm having lots (say > 10) substrings to add as condition? I'm trying to avoid using LIKE after considering this possibility

user330315 Over a year ago

Well, the use of LIKE is the core of your question. If you want a partial match on the value, there is no other way

Collectives™ on Stack Overflow

Postgresql: Selecting from list of substrings

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related