CODEWITHSUNDEEP
postgresqlfull-text-search
user3871
user3871

Reputation: 12708

Select * from to_tsvector - joining tables

I want to set the to_tsvector language (e.g.: 'French') so it uses the proper dictionary when rendering the FTS vector.

Table messages has a locale_id column, which is on the locales table. Which I then need to join the locales table to the languages table on locale_id to get the actual language name. But I'm getting an ambiguous ; error:

select * from to_tsvector(t3.language, t1.message) 
inner join message as t1 
inner join locales as t2 on (t1.locale_id = t2.id) 
inner join languages as t3 on (t2.language_id = t3.id);

ERROR: syntax error at or near ";" LINE 1: ....id)

inner join languages as t3 on (t2.language_id = t3.id);

Upvotes: 1

Views: 1053

Answers (1)

Cade Roux
Cade Roux

Reputation: 89661

newer code:

select to_tsvector(t3.language, t1.message) 
from message as t1 
inner join locales as t2 on (t1.locale_id = t2.id) 
inner join languages as t3 on (t2.language_id = t3.id);

original fix:

select * from to_tsvector(t3.language, t1.message) 
inner join message as t1 on 1 = 1 /* an ON join criteria is mandatory here even if you are doing an implicit cross join */
inner join locales as t2 on (t1.locale_id = t2.id) 
inner join languages as t3 on (t2.language_id = t3.id);

Upvotes: 1

Related Questions