Insert SQL query result into table column-CodePudding

I have this PostgreSQL table for storing words:

CREATE TABLE IF NOT EXISTS words
(
    id bigint NOT NULL DEFAULT nextval('processed_words_id_seq'::regclass),
    keyword character varying(300) COLLATE pg_catalog."default",
    trademark_blacklisted character varying(300) COLLATE pg_catalog."default",
)

insert into words (words)
VALUES ('while swam is interesting', 'ibm is a company');

CREATE TABLE IF NOT EXISTS trademarks
(
   id bigint NOT NULL DEFAULT nextval('trademarks_id_seq'::regclass),
   trademark character varying(300) COLLATE pg_catalog."default",
)

insert into words (words)
VALUES ('swam', 'ibm');

Into table trademarks I will have thousands of registered trademarks names. I want to compare words stored into words table keyword do they match not only for a words but also for word which is a in a group of words. For example:

I have a keyword while swam is interesting stored into words.keyword. I also have a trademark swam located in trademarks.trademark I have a word match so I want to detect this using SQL.

Possible solution:

select w.id, w.keyword, t.trademark 
from words w
inner join trademarks t on w.keyword::tsvector @@ 
regexp_replace(t.trademark, '\s', ' | ', 'g' )::tsquery;

I get error:

ERROR:  no operand in tsquery: "Google | "
SQL state: 42601

How I can implement this properly and insert the result into table column words.trademark_blacklisted?

CodePudding user response：

Keep it simple; cross-join the two tables and say

Where POSITION(trademark in keyword)>0

CodePudding user response：

select 
    w.id, w.keyword, t.trademark 
from
    words w
inner join 
    trademarks t 
on
  t.trademark ilike  '%'||w.keyword||'%' ;

Using the case insensitive version of LIKE.