sqlx: [PostgreSQL] citext is not compatable with text

[2020-05-01T19:29:25Z ERROR actix_http::response] Internal Server Error: mismatched types; Rust type `alloc::string::String` (as SQL type TEXT) is not compatible with SQL type 
    Caused by:
        mismatched types; Rust type `alloc::string::String` (as SQL type TEXT) is not compatible with SQL type

query:

select email, password from "user" where id = $1

Where email is citext. As a workaround, the following cast is required to cause this not to error:

select email::text, password from "user" where id = $1

However, sqlx should be able to handle citext natively.

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 9
Comments: 16 (5 by maintainers)

Most upvoted comments

Just came across this myself, too. Is anyone working on this?

mfreeborn on Sep 9, 2022

Also interested in this.

chessai on Oct 17, 2022

Honestly I think we can just add a couple checks:

if the type name is present and equals citext here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/types/str.rs#L13

if the type name is present and equals citext[] here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/types/str.rs#L31

This technically isn’t robust if someone adds a type named citext which isn’t compatible with text but… does that really matter?

This sounds like a good idea to me. Is there anyone working on this?

billy1624 on Dec 14, 2022

Is there a way to perform the cast without SQLx thinking a non nullable column is now nullable?

In case anyone else is looking for an answer, you can force a column to be treated as NOT NULL: https://docs.rs/sqlx/0.5.2/sqlx/macro.query.html#force-not-null

e.g. if column ‘email’ is of type CITEXT NOT NULL: r#“SELECT email::TEXT as “email!” FROM public.user”#

SilentByte on Apr 28, 2021

For sql to Rust, you can cast to text: (<thing>::TEXT), for Rust to sql, you can help it figure out that it wants text and then cast it to citext: $1::TEXT::CITEXT

izik1 on Nov 1, 2020

We still have to do a lookup because all we get in the responses from the protocol is the type OID, but the connection will automatically resolve and cache unknown type OIDs.

abonander on Nov 5, 2020

Honestly I think we can just add a couple checks:

if the type name is present and equals citext here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/types/str.rs#L13
if the type name is present and equals citext[] here: https://github.com/launchbadge/sqlx/blob/master/sqlx-core/src/postgres/types/str.rs#L31

This technically isn’t robust if someone adds a type named citext which isn’t compatible with text but… does that really matter?

abonander on Nov 5, 2020