CODEWITHSUNDEEP
sqlpostgresqlfull-text-searchtsvector
user287689
user287689

Reputation:

PostgreSQL - using to_tsvector with SELECT

I have been reading the PSQL Documentation and also bothering Google - although I am not sure what to look for - but it doesn't look it is possible to create a tsvector out of a select.

Let me explain a bit. I have table users and I added a tsvector column to it called tsv.

      Column       |            Type             |                     Modifiers
-------------------+-----------------------------+---------------------------------------------------
 id                | integer                     | not null default nextval('jobs_id_seq'::regclass)
 username          | character varying(255)      | not null
 tsv               | tsvector                    |

Now every user has many articles.

What I want now is to store the articles title as tsvector in the tsv column. Something like this:

UPDATE users SET tsv = to_tsvector(
  SELECT string_agg(title) 
  FROM users INNER JOIN books 
  ON user.id = articles.user_id 
  GROUP BY user.id
)

So obviously the query would not work even without trying to make a tsvector out of a SELECT. It has basically 2 "problems"

Thank you very much in advance.

Upvotes: 1

Views: 2180

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

UPDATE users
SET tsv = to_tsvector(s.tsv)
from
    (  
        SELECT id, string_agg(title) tsv
        FROM
            users
            INNER JOIN
            articles ON user.id = articles.user_id 
        GROUP BY user.id
    ) s
where users.id = s.id

Upvotes: 2

Related Questions