I’m using docker to deploy the vikunja v0.24.3 and postgres v16, and I got the error message from log like that:
2024-09-22T16:48:00.040521658Z: INFO ▶ [DATABASE] 1fe [SQL] SELECT "id", "filters", "title", "description", "owner_id", "is_favorite", "created", "updated" FROM "saved_filters" WHERE (filters LIKE '%_date%') [] - 29.376661ms
2024-09-22T16:48:00Z: ERROR ▶ 1ff [Add Task To Filter View Cron] Error fetching filters: pq: operator does not exist: json ~~ unknown
I got the error message below after I run the command directly in pg:
vikunja=# SELECT "id", "filters", "title", "description", "owner_id", "is_favorite", "created", "updated"
FROM "saved_filters"
WHERE (filters LIKE '%_date%');
ERROR: operator does not exist: json ~~ unknown
LINE 3: WHERE (filters LIKE '%_date%');
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
then I change the sql to below, everything looks good:
SELECT "id", "filters", "title", "description", "owner_id", "is_favorite", "created", "updated"
FROM "saved_filters"
WHERE ("filters"::text LIKE '%_date%');
What my “saved_filters” table schema is like below:
vikunja=# \d+ saved_filters;
Table "public.saved_filters"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+-----------------------------+-----------+----------+-------------------------------------------+----------+-------------+--------------+-------------
id | bigint | | not null | nextval('saved_filters_id_seq'::regclass) | plain | | |
filters | json | | not null | | extended | | |
title | character varying(250) | | not null | | extended | | |
description | text | | | | extended | | |
owner_id | bigint | | not null | | plain | | |
is_favorite | boolean | | | false | plain | | |
created | timestamp without time zone | | not null | | plain | | |
updated | timestamp without time zone | | not null | | plain | | |
Indexes:
"saved_filters_pkey" PRIMARY KEY, btree (id)
"IDX_saved_filters_owner_id" btree (owner_id)
"UQE_saved_filters_id" UNIQUE, btree (id)
Access method: heap
Thank you for any help. BTW, the instance is working well.