postgresql conditionally generating value for each row

Question

I am trying to implement a simple search functionalitu over links.name column. I would like to match keywords against name values and wanted to get the names with more matchings on the top of result. What I want/tried to do is

declare matchings integer := 0
select if "keyword1" ~* name then matchings := matchings + 1 end if
       if "keyword2" ~* name then matchings := matchings + 1 end if
       ... so on for all keywords given ..
       as matchings_count from links order by matchings_count;

What is the correct syntax of doing this? Not bothering about performance since links contain only 1200 rows and not going to increase more than 1500. Any inputs will be appreciated. Thanks in advance.

@jvillian, sice im on rails and seeking attention of those who are not watching postgresql tag and know stuff like this. also Answe works with activerecord query interface will be helpful since it contains two statements. — dileep nandanam
– dileep nandanam, Commented Jun 22, 2019 at 3:43
I don’t know Postgres we’ll. Is this operator - “~*”, - is a fuzzy string match? — jorge is not ai
– jorge is not ai, Commented Jun 22, 2019 at 6:11
@ЯрославМашко its very much same as like '%keyword%' in mysql, case insensitive. — dileep nandanam
– dileep nandanam, Commented Jun 22, 2019 at 6:13
Yes. Your ‘count’ on ‘case’ that returns one when it matches a keyword. You still can use ‘keyword1|keyword2’ regexp in the where clause. As a second thought I think it will be simplier. — jorge is not ai
– jorge is not ai, Commented Jun 22, 2019 at 8:39

jorge is not ai · Accepted Answer · 2019-06-22 10:30:26Z

2

The simplest way to do your task is

with t as(
 select 'key1' as k
 union all select 'key2' as k
 union all select 'key3' as k
)

select count(*) from t
where k ~* '(key1)|(key2)'
;

sqlfiddle

If you want to just count the number of matchings

select count(*) from t
where k ~* 'key1'
union all select count(*) from t
where k ~* 'key2'
...
;

edited Jun 22, 2019 at 10:30

answered Jun 22, 2019 at 9:59

jorge is not ai

1,0572 gold badges14 silver badges41 bronze badges

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

2 Comments

dileep nandanam Over a year ago

only upvoting since expecting an answer with if-then-else used. if not will accept the answer

jorge is not ai Over a year ago

You can wrap the code in CTE and do your ordering, limiting and such.

Gordon Linoff · Accepted Answer · 2019-06-22 12:34:50Z

1

In Postgres, you can convert a boolean to a number and add them up:

select l.*,
       ( ("keyword1" ~* name)::int +
         ("keyword2" ~* name)::int +
         . . .
       ) as num_matches         
from links l
order by num_matches desc;

answered Jun 22, 2019 at 12:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

dileep nandanam Over a year ago

Thanks, that served the purpose.

Collectives™ on Stack Overflow

postgresql conditionally generating value for each row

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related