CODEWITHSUNDEEP
postgresqlindexing
ichigolas
ichigolas

Reputation: 7725

Postgres: syntax error creating index using prgm GIN

Following the instructions at Postgres trgm docs, I'm able to create indexes using both GIN and GiST:

home_accounting_dev=# \d+ test_trgm
                     Table "public.test_trgm"
 Column | Type | Modifiers | Storage  | Stats target | Description 
--------+------+-----------+----------+--------------+-------------
 t      | text |           | extended |              | 
Indexes:
    "trgm_idx" gist (t gist_trgm_ops)
    "trgm_idx_2" gin (t gin_trgm_ops)
Has OIDs: no

... but I can't seem to do so in an existent table "expenditures", column "desc".

home_accounting_dev=# \d+ expenditures
                                                          Table "public.expenditures"
   Column    |            Type             |                         Modifiers                         | Storage  | Stats target | Description 
-------------+-----------------------------+-----------------------------------------------------------+----------+--------------+-------------
 id          | integer                     | not null default nextval('expenditures_id_seq'::regclass) | plain    |              | 
 desc        | text                        |                                                           | extended |              | 
 amount      | character varying(255)      |                                                           | extended |              | 
 inserted_at | timestamp without time zone | not null                                                  | plain    |              | 
 updated_at  | timestamp without time zone | not null                                                  | plain    |              | 
 expent_at   | date                        |                                                           | plain    |              | 
Indexes:
    "expenditures_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "expenditures_taggings" CONSTRAINT "expenditures_taggings_assoc_id_fkey" FOREIGN KEY (assoc_id) REFERENCES expenditures(id)
Has OIDs: no

The column is exactly the same type and characteristics as this example "t" column in the table test_trgm, but it throws a syntax error when I try to create the index:

home_accounting_dev=# CREATE INDEX asdf_qwer ON expenditures USING gin (desc gin_trgm_ops);
ERROR:  syntax error at or near "desc"
LINE 1: CREATE INDEX asdf_qwer ON expenditures USING gin (desc gin_t...

It fails differently if I misstype the column name:

home_accounting_dev=# CREATE INDEX asdf_qwer ON expenditures USING gin (dec gin_trgm_ops);
ERROR:  column "dec" does not exist

Upvotes: 2

Views: 1590

Answers (1)

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

desc is a reserved word. Enclose it in double quotes and it will work:

CREATE INDEX asdf_qwer ON expenditures USING gin ("desc" gin_trgm_ops);

Upvotes: 3

Related Questions