Postgres query with LIKE

[Tomelin]

Hi all,

I have the Golang+Postgres+SQLC e I need create the query with LIKE but the postgres syntaxe is not accepet in SQLC queries. I try this format:

-- name: FindOrganization :many
SELECT * FROM organization
WHERE deleted_at IS NULL and name LIKE % $1 % or description=$2 or tag LIKE $3;

[go-aegian]

1- Install the following Postgres extensions

create extension if not exists btree_gin;
create extension if not exists pg_trgm;

2- Modify organization table by adding these 2 indexes

create index if not exists ix_organization_name_trgm on organization using gin (name gin_trgm_ops);
create index if not exists ix_organization_tag_trgm on organization using gin (tag gin_trgm_ops);

3- Rewrite the query as below

-- name: FindOrganization :many
select * 
from organization
where deleted_at is null and 
          (
            name ilike concat('%', @name_match::text, '%') or
            description=$2 or 
            tag ilike concat('%', @tag_match::text, '%')
          );