I start reading Gregory Smith book « PostgreSQL 9.0 High performance » some days ago. Quickly, he's talking about this very popular issue when upgrading to version 8.3 and bellow : the removal of some implicit casts from text types to time or numerical ones in 8.3. Obviously, the only clean solution here is to fix the application itself.
However, for those that cannot afford quickly such a work, it also points to a popular quick workaround that and suffer from a side effect. Then, it remind me I found another quick-n-dirty fix about a year ago I spoke a bit on our favorite chan. Again, keep in mind that the only clean way to go is fix your application if you hit this problem !
So here is the problem:
casts=# CREATE TABLE test AS SELECT generate_series(1,10) AS id;
SELECT
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR: operator does NOT exist: integer = text
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
^
TIPS : No operator matches the given name AND argument type(s). You might need TO ADD explicit type casts.
The very well known solution is to recreate some of these implicit casts that were removed in 8.3. Peter Eisentraut blogged about that, you'll find his SQL script here.
However, as some users noticed in the comments, there is a side effect bug with this solution: it can breaks the concatenation operator:
casts=# BEGIN ;
BEGIN
casts=# \i /tmp/implicit_casts.sql
CREATE FUNCTION
CREATE CAST
-- [...]
CREATE FUNCTION
CREATE CAST
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR: operator IS NOT UNIQUE: unknown || integer
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
^
TIPS : Could NOT choose a best candidate operator. You might need TO ADD explicit type casts.
casts=# ROLLBACK ;
ROLLBACK
Oops.
From here, the solution could be to cast one of the operand :
casts=# SELECT id, 'value = ' || id::text FROM test WHERE id = '5'::text;
5 | value = 5
But then, we are back to the application fix where it might worth spending more time fixing things in the good way.
There is another solution: creating missing operators instead of implicit casts. Here is a sql file with a lot of those operators:
operators_workaround.sql and a sample with text to integer:
CREATE FUNCTION pg_catalog.texteqint(text, integer) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.inteqtext(integer, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($1)) = $2;$$;
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.inteqtext, LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
And here is the same previous test:
casts=# BEGIN ;
BEGIN
casts=# \i /tmp/create_operators.sql
CREATE FUNCTION
-- [...]
CREATE FUNCTION
CREATE OPERATOR
-- [...]
CREATE OPERATOR
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
5 | value = 5
casts=# -- what, you don't trust me ?
casts=# ROLLBACK ;
ROLLBACK
casts=# SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
ERROR: operator does NOT exist: integer = text
LINE 1 : SELECT id, 'value = ' || id FROM test WHERE id = '5'::text;
^
TIPS : No operator matches the given name AND argument type(s). You might need TO ADD explicit type casts.
Same advice from Peter here: if possible, only create the operators you need to fix your application !
So far, I only had one positive feedback about this workaround about a year ago, and I don't consider this is enough to actually claim it is a safe solution. So please, comments, tests and reports are welcome !