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?
asked Nov 12, 2014 at 18:32
2
  • 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. Commented Nov 13, 2014 at 0:32
  • 1
    @CraigRinger I think this is more or less the full answer one can give here. Commented Nov 17, 2014 at 16:26

1 Answer 1

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
...
answered Apr 30, 2017 at 13:08

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.