Postgresql Wildcard Like for Any of a List of Words

PostgreSQL wildcard LIKE for any of a list of words

You can use Postgres' SIMILAR TO operator which supports alternations, i.e.

select * from table where lower(value) similar to '%(foo|bar|baz)%';

PostgreSQL wildcard LIKE for any of a list of words returned by Subquery

select *
from people
where name like any (
    select concat(last_name, ', ', first_name, '%')
    from other_people
)

Postgresql LIKE ANY versus LIKE

This is a bit speculative. I think the key is your pattern:

where words.text LIKE 'test%'

Note that the like pattern starts with a constant string. The means that Postgres can do a range scan on the index for the words that start with 'test'.

When you then introduce multiple comparisons, the optimizer gets confused and no longer considers multiple range scans. Instead, it decides that it needs to process all the rows.

This may be a case where this re-write gives you the performance that you want:

select id
from words
where words.text LIKE 'word1%'
union
select id
from words
where words.text LIKE 'another%'
union 
select id
from words
where words.text LIKE 'third%';

Notes:

• The distinct is not needed because of the union.
• If the pattern starts with a wildcard, then a full scan is needed anyway.
• You might want to consider an n-gram or full-text index on the table.

Get word frequency rankings of words in postgreSQL and filter by label

Your new result shows you want to count and rank per day, word and label. This means you just have to add label to your GROUP BY and PARTITION BY clauses (and probably to ORDER BY, too).

select *
from 
(
  select 
    date_trunc('day', created_at) as created_day, 
    word, 
    label, 
    count(*) as cnt,
    rank() over(partition by date_trunc('day', created_at), label order by count(*) desc) as rn
  from dummy d
  cross join lateral regexp_split_to_table
  (
    trim(regexp_replace(tweet, '\y(rt|co|https|amp|none)\y', '', 'g')),
    '\s+'
  ) w(word)
  group by created_day, word, label
) t
where (created_day > current_date - interval '10 days') and word is not null
order by created_day desc, label, rn;

PostgreSQL: Where clause using LIKE, ANY, and wildcards

You have to unnest the array field:

with my_table(my_field) as (
values
    (array['query medium', 'large query']),
    (array['large query', 'small query'])
)
select t.* 
from my_table t,
lateral unnest(my_field) elem
where elem ilike 'query%';

            my_field            
--------------------------------
 {"query medium","large query"}
(1 row)

---

Related Topics