Contains query not working: Operator does not exist: text @> unknown

[acupofjose]

Environment

• PostgreSQL version: 14
• PostgREST version: 12.0.2
• Operating system: Linux (WSL)

Description of issue

Re: supabase-community/supabase-csharp#140

Given the following schema on a fresh docker install of postgres 14 and postgrest:

-- USERS
CREATE TYPE public.user_status AS ENUM ('ONLINE', 'OFFLINE');
CREATE TABLE public.users
(
    username         text primary key,
    inserted_at      timestamp without time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    updated_at       timestamp without time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
    favorite_numbers int[]                       DEFAULT null,
    favorite_name    text UNIQUE                                                      null,
    data             jsonb                       DEFAULT null,
    age_range        int4range                   DEFAULT null,
    status           user_status                 DEFAULT 'ONLINE'::public.user_status,
    catchphrase      tsvector                    DEFAULT null
);

INSERT INTO public.users (username, status, age_range, catchphrase)
VALUES ('supabot', 'ONLINE', '[1,2)'::int4range, 'fat cat'::tsvector),
       ('kiwicopple', 'OFFLINE', '[25,35)'::int4range, 'cat bat'::tsvector),
       ('awailas', 'ONLINE', '[25,35)'::int4range, 'bat rat'::tsvector),
       ('acupofjose', 'OFFLINE', '[25,35)'::int4range, 'bat rat'::tsvector),
       ('dragarcia', 'ONLINE', '[20,30)'::int4range, 'rat fat'::tsvector);

And the following request:

http://localhost:3000/users?username=cs.{dragarcia,acupofjose}

The following is returned from the API:

{
   "code":"42883",
   "details":null,
   "hint":"No operator matches the given name and argument types. You might need to add explicit type casts.",
   "message":"operator does not exist: text @> unknown"
}

[laurenceisla]

The contains operator @> does not work for text types. It's used for arrays, json(b) and others. Are you trying to query all the users with username that belong to a list of candidates? If so you can use like(any):

http://localhost:3000/users?username=like(any).{dragarcia,acupofjose}

[acupofjose]

Ah, makes total sense! Misunderstanding on my part. Thanks @laurenceisla!