4

Coming from this topic on SO a user suggest me to enable implicit casts in my PostgreSQL and I don't know how to do that. I also don't know if this is a good practice or if it's the solution to the issues I'm having but I want to try this one since others solutions isn't working. So find information on Google I found this:

For enable implicit casts you must therefore execute the following commands in your PostgreSQL console when connected to the template1 database, so that any database created afterward will come with the required CASTs (if your database is already created, execute the commands in your database as well):

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;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';

CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

So my questions around this:

  1. How do I execute that on template1? Any example will be helpful since I'm not a DBA just a simple developer and most of the time my tool & friend is pgAdmin or any other GUI on Windows
  2. Can this be done easily from pgAdmin or any other GUI for Postgres or I need to work from command line?
  3. Is this a good practice? Will be any performance issues afterwards?
  4. Are this the only one queries I need to run to get ride of my problem?
  • 8
    It's not generally a good idea as it can have possibly confusing side-effects elsewhere in the database system, affecting things like choice of overloaded functions, and it can cause some incorrect queries to silently succeed. Implicit casts to text were removed for a reason. There'll be no performance impact. – Craig Ringer Nov 13 '14 at 0:32
  • 1
    @CraigRinger I think this is more or less the full answer one can give here. – dezso Nov 17 '14 at 16:26
1

One can also just add ?compatible=7.1 to a jdbc connection url (before the 42 release)... people say 'Implicit casts to text were removed for a reason' but in 10 years I have never had an issue with them. (I have fixed other bugs is pgsql though)

https://www.postgresql.org/docs/current/static/sql-createcast.html

\dC
********* QUERY **********
SELECT pg_catalog.format_type(castsource, NULL) AS "Source type",
       pg_catalog.format_type(casttarget, NULL) AS "Target type",
       CASE WHEN castfunc = 0 THEN '(binary coercible)'
            ELSE p.proname
       END as "Function",
       CASE WHEN c.castcontext = 'e' THEN 'no'
            WHEN c.castcontext = 'a' THEN 'in assignment'
            ELSE 'yes'
       END as "Implicit?"FROM pg_catalog.pg_cast c LEFT JOIN pg_catalog.pg_proc p
     ON c.castfunc = p.oid
     LEFT JOIN pg_catalog.pg_type ts
     ON c.castsource = ts.oid
     LEFT JOIN pg_catalog.pg_namespace ns
     ON ns.oid = ts.typnamespace
     LEFT JOIN pg_catalog.pg_type tt
     ON c.casttarget = tt.oid
     LEFT JOIN pg_catalog.pg_namespace nt
     ON nt.oid = tt.typnamespace
WHERE ( (true  AND pg_catalog.pg_type_is_visible(ts.oid)
) OR (true  AND pg_catalog.pg_type_is_visible(tt.oid)
) )
ORDER BY 1, 2;
**************************
                                         List of casts
         Source type         |         Target type         |      Function      |   Implicit?   
-----------------------------+-----------------------------+--------------------+---------------
 abstime                     | date                        | date               | in assignment
 abstime                     | integer                     | (binary coercible) | no
 abstime                     | timestamp without time zone | timestamp          | yes
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.