luckyframework / luckyframework/avram

Support Array of citext `citext[]` column

Open
#970 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

hacktoberfest
Dominant language
Crystal
Stars
183
Forks
67
PR merge metrics
No merged PRs in 30d

Description

Avram currently supports `Array(String)`, but if you need to search an array with case insensitive, then things get a little hairy

(assuming you have a GIN index on this column...)

```
=# select username from users where tags @> array['Lucky'];
username
-------------
jeremy
(1 row)

Time: 0.438 ms
=# select username from users where tags @> array['LUCKY'];
username
-------------
(0 rows)

Time: 0.815 ms
=# select username from users where lower(tags::text)::text[] @> array['lucky'];
username
-------------
jeremy
(1 row)

Time: 36.088 ms
```

Now if you use a `citext[]` column instead..

```
=# select username from users where tags @> array['LUCKY']::citext[];
username
-------------
jeremy
(1 row)

Time: 0.395 ms
=# select username from users where tags @> array['lUcKy']::citext[];
username
-------------
jeremy
(1 row)

Time: 0.408 ms
```

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by tracing how Avram currently supports Array(String) columns and how PostgreSQL citext types are represented. Use the query examples in this issue to define the expected case-insensitive behavior for citext[]; done when such columns can be queried without the lower(tags::text) workaround.

Written by the indexing model from the issue text.

Assessment

Tech stack
crystal, postgresql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.