CODEWITHSUNDEEP
sqlpostgresql
Peter Penzov
Peter Penzov

Reputation: 1586

Insert SQL query result into table column

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 (keyword,trademark_blacklisted)
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 (keyword,trademark_blacklisted)
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? Is it possible to limit it to search by param? For example search for only one word is it blacklisted?

Upvotes: 0

Views: 221

Answers (3)

Harsimran Singh
Harsimran Singh

Reputation: 359

I am new in this area, but you can try the below query.

INSERT INTO words (trademark_blacklisted)
SELECT t.trademark
FROM words w
INNER JOIN trademarks t ON t.trademark ILIKE '%'||w.keyword||'%'
WHERE w.keyword = 'swam'

Upvotes: 0

Adrian Klaver
Adrian Klaver

Reputation: 19665

select 
    w.id, w.keyword, t.trademark 
from
    words w
inner join 
    trademarks t 
on
  t.trademark ilike  '%'||w.keyword||'%' 
where 
   w.keyword = <some_value>;

Using the case insensitive version of LIKE.

Upvotes: 1

Chris Maurer
Chris Maurer

Reputation: 2547

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

Where POSITION(trademark in keyword)>0

Upvotes: 0

Related Questions