Obviously, a lot of people are having trouble with the removal of many implicit casts in PostgreSQL 8.3. While this will lead to more robust applications in the future, it will prevent many people from moving to 8.3 altogether at the moment. The problem why you can't simply make the casts implicit again is that in a somewhat unrelated development, 8.3 will generate a cast from and to text automatically (well, implicitly) for any data type, which is why most of the casts in question have been removed from the pg_cast catalog altogether and you can't simply run an UPDATE command to put them back the way you want.
I have used a shell script to regenerate the removed casts including the required cast functions. The result is pg83-implicit-casts.sql. I have tested this against some of the recent "bug reports" I found on the mailing lists, so it should work for some people at least. If someone wants to use this, I suggest you only add the casts you really need, not the whole file.
(Note: This blog entry was recovered after a server crash and does not include any of the original comments. Those comments contained additional important insights about the restoration of the casts, which have unfortunately been lost now. The gist was, only restore the casts you need, not all of them.)
Tuesday, March 4, 2008
Subscribe to:
Post Comments (Atom)
Posts
Hi,
ReplyDeleteI noticed that using these "comptability casts" for Postgresql 8.3 can cause some problems when concatenating strings and non-strings, as explained here:
http://www.postgresql.org/docs/current/static/functions-string.html
Example:
--------------------
CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
select 'value = ' || 123
--------------------
ERROR: operator is not unique: unknown || integer
LINE 4: select 'value = ' || 123
^
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
--------------------
Without the casts, concatenation without explicit cast works just fine:
--------------------
select 'value = ' || 123
--------------------
value = 123
--------------------
Hi, you could help me with this error:
Deletepg_query(): Query failed: ERROR: el operador no es �nico: d_valor_numerico || unknown LINE 8: cast(p.valor_1||' '||m.simbolo as varchar) as valor1_comple... ^ HINT: No se pudo escoger el operador m�s adecuado. Puede ser necesario agregar conversiones expl�citas de tipos.
Thank you for your superbly simple and helpful typecasting sqls.
ReplyDeleteI'm trying to get my EXECUTE function to work and is giving me type cast errors and has me stumped. And I'm not sure how to align the casting properly.
ReplyDeleteI have a trigger that calls a function. In my trigger I dynamically create my select statement.
My Variable:
array_details character varying[];
I populate it with {a,b}
Hard-coded (Works):
SELECT catalogue.cg_bdd (ARRAY['a','b']);
Dynamically Created (Fails):
EXECUTE 'SELECT catalogue.cg_bdd '(' || array_details || ');';
Error:
ERROR: operator does not exist: text || character varying[]
LINE 1: SELECT 'SELECT ' || $1 || '(' || $2 || ');'
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT 'SELECT ' || $1 || '(' || $2 || ');'
Exist some option to set the 8.2 cast compatibility, some like put a option at the pg conf files?
ReplyDelete@Milton
ReplyDeleteI don't have any experiences with 8.2 cast compatibility. There is no simple parameter, but I imagine the recipe described here for 8.3 should apply similarly, meaning you can add (or remove) the missing casts, as the case may be.
What's the difference between implicit cast and explicit cast?
ReplyDeleteI want an explicit type cast for character varying = integer error. When I write mycolumn=?::text(or)mycolumn=?::char(or)
ReplyDeletemycolumn=?::character varying(20)(or)mycolumn=?::string in my sql statement .But,not ok.What i want to know is how to type cast explicitly from integer to character varying.
Hi,
ReplyDeleteI create insert function but when that execute for insert data that time generate error this function does not exit. what problem in function execution?
My function
CREATE OR REPLACE FUNCTION insertuserlogin(userloginidp integer, usercategoryidf integer, usertypeidf integer, usertypereferenceidf integer, loginname character varying, loginpassword character varying, menutypeidf integer, username character varying, dashboardconfig text, careprovideridf integer, isactive boolean)
RETURNS void AS
$BODY$BEGIN
INSERT INTO "tbuserlogin" VALUES (userloginidp,usercategoryidf,usertypeidf,usertypereferenceidf,loginname,loginpassword,menutypeidf,username,dashboardconfig,careprovideridf,isactive);
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION insertuserlogin(integer, integer, integer, integer, character varying, character varying, integer, character varying, text, integer, boolean)
OWNER TO postgres;
Insert Data Query
SELECT insertuserlogin(1,2,4,3,'fg','sdf',6,'err','fgsd',7);
Error
RROR: function insertuserlogin(integer, integer, integer, integer, unknown, unknown, integer, unknown, unknown, integer) does not exist
LINE 1: SELECT insertuserlogin(1,2,4,3,'fg','sdf',6,'err','fgsd',7);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.