Wednesday, March 5, 2008

Readding implicit casts in PostgreSQL 8.3

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.)


  1. Hi,

    I noticed that using these "comptability casts" for Postgresql 8.3 can cause some problems when concatenating strings and non-strings, as explained here:

    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

    1. Hi, you could help me with this error:

      pg_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.

  2. Thank you for your superbly simple and helpful typecasting sqls.

  3. I'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.

    I 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: 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 || ');'

  4. Exist some option to set the 8.2 cast compatibility, some like put a option at the pg conf files?

  5. @Milton

    I 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.

  6. What's the difference between implicit cast and explicit cast?

  7. I want an explicit type cast for character varying = integer error. When I write mycolumn=?::text(or)mycolumn=?::char(or)
    mycolumn=?::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.

  8. Hi,
    I 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
    INSERT INTO "tbuserlogin" VALUES (userloginidp,usercategoryidf,usertypeidf,usertypereferenceidf,loginname,loginpassword,menutypeidf,username,dashboardconfig,careprovideridf,isactive);
    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);


    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.