sql: missing operator support with collated strings and CIText

We currently are missing support for different things with collated strings and CIText that PG supports:

- [ ] `length` builtin function
- [ ] `||` binary operator
- [ ] `=` comparison operator
- [ ] `~` comparison operator
- [ ] `~*` comparison operator
- [ ] `array_agg` aggregate function

```sql
-- replace CITEXT with TEXT COLLATE "en_US" for collated strings
CREATE TABLE test_compat (
id INT PRIMARY KEY,
username CITEXT,
email CITEXT
);

INSERT INTO test_compat (id, username, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'BOB', 'bob@test.org'),
(3, 'charlie', 'charlie@domain.net');

-- CockroachDB: ERROR - unknown signature: length(citext)
SELECT username, LENGTH(username), UPPER(username), LOWER(username)
FROM test_compat WHERE username = 'alice';

-- CockroachDB: ERROR - unsupported binary operator: ||
SELECT username || ' - ' || email as combined FROM test_compat;

-- CockroachDB: ERROR - unsupported comparison operator: =
SELECT 'foo'::CITEXT = 'bar'::TEXT;

-- CockroachDB: ERROR - unsupported comparison operator: ~
SELECT username FROM test_compat WHERE username ~ '^[Aa]lice';

-- CockroachDB: ERROR - unsupported comparison operator: ~*
SELECT username FROM test_compat WHERE username ~* '^alice';

-- CockroachDB: ERROR - unknown signature: array_agg(citext)
SELECT array_agg(username) FROM test_compat;
```

Jira issue: CRDB-53138

Epic CRDB-65892