How to implement Postgres array search with ILIKE?

Question

PostgreSQL

I have a table like this in a database

CREATE TABLE public."Releases" (
    id uuid NOT NULL,
    name character varying(150) COLLATE pg_catalog."default" NOT NULL,
    english character varying(150) COLLATE pg_catalog."default",
    synonyms character varying(150)[] COLLATE pg_catalog."default" NOT NULL DEFAULT (ARRAY[]::character varying[])::character varying(150)[]
) WITH (
    OIDS = FALSE
)

TABLESPACE pg_default;

ALTER TABLE public."Releases"
    OWNER to postgres;

It is very easy to implement search using ILIKE for name, english fields. Only now a search was needed in the synonyms array, and here, accordingly, a situation arose with such a request:

SELECT * FROM "public"."Releases" WHERE synonyms ILIKE '%test%'

An error occurs:

ОШИБКА:  оператор не существует: character varying[] ~~* unknown
СТРОКА 1: SELECT * FROM "public"."Releases" WHERE synonyms ILIKE '%tes...
                                                           ^
ПОДСКАЗКА:  Оператор с данными именем и типами аргументов не найден. Возможно, вам следует добавить явные приведения типов.

So how exactly do you edit the query to add this behavior?

Answer 1

You can try the array_to_string() function :

SELECT * FROM "public"."Releases" WHERE array_to_string(synonyms, ', ') ILIKE '%test%';