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:
- 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 - Can this be done easily from pgAdmin or any other GUI for Postgres or I need to work from command line?
- Is this a good practice? Will be any performance issues afterwards?
- Are this the only one queries I need to run to get ride of my problem?
-
8It'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– Craig Ringer2014年11月13日 00:32:45 +00:00Commented Nov 13, 2014 at 0:32
-
1@CraigRinger I think this is more or less the full answer one can give here.András Váczi– András Váczi2014年11月17日 16:26:23 +00:00Commented Nov 17, 2014 at 16:26
1 Answer 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
...
Explore related questions
See similar questions with these tags.