Casting column with ::text not working as expected

[skoobasteeve]

Environment

• PostgreSQL version: 14.5 - RDS
• PostgREST version: 2.15.1 - Docker postgrest/postgrest
• Operating system: Ubuntu 22.04.1

Description of issue

One of the columns in my tables is an xml data type, and in order to do text-based queries on that column, I need to convert it to text in the query. I can achieve this with the below SQL for example:

SELECT xml FROM api.accounts WHERE xml::text LIKE '%raymond%';

To do something similar in postgrest, I tried the below API call:

curl "https://my.example.com/accounts?select=xml::text&xml=like.*raymond*" \
-H 'Accept: application/json'

But I get an error in response:

{
  "code": "42883",
  "details": null,
  "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
  "message": "operator does not exist: xml ~~ unknown"
}

Any idea if this is an issue with Postgrest or if I'm doing something wrong?

Thank you!

[steve-chavez]

@skoobasteeve The error message says is not related to the cast, but to the usage of the like operator(underlyingly it uses ~~) on the xml column.

On your curl request you're basically doing:

SELECT xml::text FROM api.accounts WHERE xml LIKE '%raymond%';`

Casting on filters is not supported directly because it invalidates indexes on the columns. For more details see #1952.

You can use a computed column for this.

[skoobasteeve]

@steve-chavez I see now, thank you for confirming that. For anyone who wants to do this in the future, I was able to create a function based on the example in the docs:

CREATE FUNCTION api.xml_text(api.accounts) RETURNS text AS $$
	SELECT $1.xml;
$$ LANGUAGE SQL;

Then, I can add xml_text=fts.raymond or xml_text=like.*raymond* as a parameter and it works great! (Full disclosure, I'm an SQL noob so @steve-chavez let me know if there's anything blatantly wrong with this function)

[steve-chavez]

Cool! LGTM, if you have a many rows on that table you might want to add an index as mentioned on computed columns.

Can be done like:

CREATE INDEX date_index ON table_name (CAST(varchar_column AS DATE));

[skoobasteeve]

@steve-chavez Great tip, thank you!