Signiant Support

Custom Solution ERROR: operator does not exist: numeric = text Print


Symptoms

After upgrading to 9.2, my custom built solution fails with the following error:

ERROR:  operator does not exist: numeric = text
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 

Resolution

This issue occurs because PostgreSQL 9.0 does not automatically cast non-character data types to TEXT. Signiant 9.2 has been upgraded to use PostgreSQL 9.0 compared to PostgreSQL 8.1 used in earlier versions. 

Starting in PostgreSQL 8.3, non-character data types are no longer automatically cast to text. Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text,for most built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. 

So for example, the following query would work in Signiant 8.x but not 9.2 since node_id is numeric where prop_val is text:

SELECT node_name FROM web_user_property, node  WHERE node_id=prop_val;

To make this work in Signiant 9.2, the query would need to be changed to something like:

SELECT node_name FROM web_user_property, node  WHERE (CAST (node_id AS text))=prop_val;
or
SELECT node_name FROM web_user_property, node  WHERE node_id=(CAST (prop_val AS numeric));
 

Applies To

Signiant Managers, Version 9.2 and higher