Question 90413 in PostgreSQL Question posted in
The official documentation can be found here.

Insert SQL query result into table column – Postgresql

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?

Tags:

3

Answers


  1. 0

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

    Where POSITION(trademark in keyword)>0
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
  3. 0

    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'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search