sql: Support `CITEXT` type alias for case-insensitive text

[bdarnell]

We have limited support for case-insensitive column types via a special and undocumented collation. Postgres has a CITEXT type for this purpose. Supporting CITEXT as an alias for TEXT COLLATE en_u_ks_level2 would make this much more discoverable and usable.

gz#11468

Jira issue: CRDB-5855

[jordanlewis]

Good idea. Note we'll have to make them interact with collations properly as well, which seems slightly confusing:

As of PostgreSQL 9.1, you can attach a COLLATE specification to citext columns or data values. Currently, citext operators will honor a non-default COLLATE specification while comparing case-folded strings, but the initial folding to lower case is always done according to the database's LC_CTYPE setting (that is, as though COLLATE "default" were given). This may be changed in a future release so that both steps follow the input COLLATE specification.

[bdarnell]

Yeah. That doesn't seem too bad, though. Just attach _u_ks_level2 to whatever base collation is specified (and disallow any non-basic collations)

[jordanlewis]

This might be a good starter project.

[tarekbadrsh]

I need to start work on this issue , please any one give me hints how to get started.

[tarekbadrsh]

@knz @jordanlewis
i need your help how to get started work on this issue.

[shaqq]

👋 @bdarnell, I was thinking about tackling this issue... but I'm also new to the codebase.

I'm assuming you want to avoid a hacky approach, like looking for the string CITEXT in the original sql statement and replacing it with TEXT COLLATE en_u_ks_level2 (that also wouldn't allow you to add a COLLATE statement to an existing CITEXT column)

From reading the tech notes, it seems like I'd have to modify the parser/sql.y to recognize the CITEXT token and... perhaps call it out as something special. And then in sem/tree/normalize.go look for the CITEXT token in the AST and swap it out by adding _u_ks_level2 to the base collation?

I don't feel like I quite have it right, but I wanted to ask before getting into a rabbit hole