Can't compare strings when COLLATE'ing (But I can when using pgx/v5??)

[Nintron27]

I have the following table structure:

CREATE TABLE IF NOT EXISTS "user"
(
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(32) NOT NULL UNIQUE COLLATE "en-US-u-ks-level2",
    password TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

I'm trying to run the following query:

SELECT created_at FROM "user" WHERE username = 'John';

However, I get the following error:
ERROR: executing stmt 1: run-query-via-api: unsupported comparison operator: = SQLSTATE: 22023

And in my SQL editor I get the error: unsupported comparison operator: <collatedvarchar{en-US-u-ks-level2}> = <string>

However, the wackiest of all, in my Golang project I can perform this query fine against the same exactly database. I'm using SQLC with the pgx/v5 package. I'm quite stumped on this, if anyone has any ideas/suggestions I'd appreciate them.

[yuzefovich]

In short, what happens with the problematic statement is that untyped string 'John' is treated by CRDB as STRING type, and comparing a collated string against a "regular" string isn't supported, so you must help the type checker a bit (e.g. by adding an explicit cast).

My guess is that sqlc automatically adds the casts for you, so the statement that is actually executed is not

SELECT created_at FROM "user" WHERE username = 'John';

but is something like:

SELECT created_at FROM "user" WHERE username = 'John'::VARCHAR(32) COLLATE "en-US-u-ks-level2";

which works.

[Nintron27]

Looking at the SQL that is showing up in the SQL history on the CRDB dashboard it doesn't show the COLLATE, but I agree that this must be the way that SQLC is handling this (not sure if that history just doesn't show certain parts of statements). Regardless, this is what works for me, so thanks for the further clarification!

[zhixinwen]

What if I want to do something like:

CREATE TYPE IF NOT EXISTS age_name AS (x INT8, y STRING(64));
SELECT created_at FROM "user" WHERE ("age", "username" ) = ANY($1::age_name[]);

and I am seeing:

"unsupported comparison operator: <tuple{int, collatedstring{en_us_u_ks_level2}}> = ANY <tuple{int AS x, string AS y}[]>"

but looks like I cannot CREATE TYPE with collation