Thread: syntax error at or near "gist_trgm_ops" on debian

Hello Postgres users,

I'm Philippe from switzerland,

I want to build a trigram index in a mycology database of mine, 
currently running on a debian laptop, and facing the following issue:


diskpix=> CREATE INDEX idx_trgm_genus ON myco.genus USING GIST 
(myco.genus.name gist_trgm_ops);
ERROR:  syntax error at or near "gist_trgm_ops"
LINE 1: ...m_genus ON myco.genus USING GIST (myco.genus.name gist_trgm_...


I've installed the postgresql-contrib package, rebooted my box, then 
created the trgm extension using:


CREATE EXTENSION pg_trgm;


But it still does not work, what am I missing?

-- 
Philippe Strauss
https://straussengineering.ch/

Le 10/05/2024 à 14:00, Philippe Strauss a écrit :
> Hello Postgres users,
>
> I'm Philippe from switzerland,
>
> I want to build a trigram index in a mycology database of mine,
> currently running on a debian laptop, and facing the following issue:
>
>
> diskpix=> CREATE INDEX idx_trgm_genus ON myco.genus USING GIST
> (myco.genus.name gist_trgm_ops);
> ERROR:  syntax error at or near "gist_trgm_ops"
> LINE 1: ...m_genus ON myco.genus USING GIST (myco.genus.name
> gist_trgm_...
>
>
> I've installed the postgresql-contrib package, rebooted my box, then
> created the trgm extension using:
>
>
> CREATE EXTENSION pg_trgm;
>
>
> But it still does not work, what am I missing?
>
Hello Philippe,

Perhaps you should try using ONLY the name of the column to index, not
specifying the table :

CREATE INDEX idx_trgm_genus ON myco.genus USING GIST (name gist_trgm_ops);

Yours,
François