Currently, this we are running in production, not faced any issues with functional or performance or database maintenance, I am talking about banking related application
As per my knowledge/experience this should work without any downside, and, this cast creation method is part of postgresql document from 8.4 url: http://www.postgresql.org/docs/9.4/static/sql-createcast.html Thanks Sridhar BN On Sun, Feb 22, 2015 at 7:39 AM, Ken Tanzer <ken.tan...@gmail.com> wrote: > I tried that and it does indeed work. (With, of course, the appropriate > permissions to create the cast.) > > So this makes me wonder--is there any downside or unwelcome side effects > to having such a cast? And if not, why isn't it part of the default setup? > > Cheers, > Ken > > On Sat, Feb 21, 2015 at 3:34 AM, sridhar bamandlapally < > sridhar....@gmail.com> wrote: > >> Hi >> >> Please see below, this works, way for implicit type casting >> >> bns=# CREATE TEMP TABLE foo (my_array varchar[]); >> CREATE TABLE >> bns=# >> bns=# INSERT INTO foo (my_array) SELECT '{TEST}'; >> INSERT 0 1 >> bns=# >> bns=# SELECT my_array[1],array_length(my_array,1) FROM foo; >> my_array | array_length >> ----------+-------------- >> TEST | 1 >> (1 row) >> >> bns=# >> *bns=# INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';* >> *ERROR: column "my_array" is of type character varying[] but expression >> is of type text* >> *LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}';* >> * ^* >> *HINT: You will need to rewrite or cast the expression.* >> bns=# >> bns=# CREATE CAST (text AS varchar[]) WITH INOUT AS IMPLICIT; >> CREATE CAST >> bns=# >> bns=# INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'; >> ---- this works >> INSERT 0 1 >> bns=# >> bns=# >> >> >> in previous mail, sorry for not mentioning varchar*"[]"* >> >> We did type cast implicit method to avoid application code changes for >> Oracle to PostgreSQL compatible >> >> Thanks >> Sridhar BN >> >> >> >> On Sat, Feb 21, 2015 at 9:38 AM, Ken Tanzer <ken.tan...@gmail.com> wrote: >> >>> I'm not able to run this unless I'm the Postgres super user. But if I >>> run it as such, it tells me that cast already exists anyway. >>> >>> CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT; >>> ERROR: cast from type text to type character varying already exists >>> >>> Of course this will work fine: >>> INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'::varchar[]; >>> >>> I was mostly surprised by having DISTINCT added to a SELECT make things >>> break. It may be too obscure an issue to be worth adding, but nothing on >>> the DISTINCT documentation suggests this possibility. >>> >>> "If DISTINCT is specified, all duplicate rows are removed from the >>> result set..." >>> http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT >>> >>> Cheers, >>> Ken >>> >>> On Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally < >>> sridhar....@gmail.com> wrote: >>> >>>> >>>ERROR: column "my_array" is of type character varying[] but >>>> expression is of type text >>>> >>>> please try this below, may be this should help >>>> >>>> CREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT; >>>> >>>> just for info: >>>> actually this should be available in default >>>> >>>> >>>> On Fri, Feb 20, 2015 at 9:48 AM, Ken Tanzer <ken.tan...@gmail.com> >>>> wrote: >>>> >>>>> Hi. Here's a boiled down example of something that caught me by >>>>> surprise: >>>>> >>>>> ag_reach_test=> CREATE TEMP TABLE foo (my_array varchar[]); >>>>> CREATE TABLE >>>>> ag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}'; >>>>> INSERT 0 1 >>>>> ag_reach_test=> SELECT my_array[1],array_length(my_array,1) FROM foo; >>>>> my_array | array_length >>>>> ----------+-------------- >>>>> TEST | 1 >>>>> (1 row) >>>>> >>>>> ag_reach_test=> INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'; >>>>> ERROR: column "my_array" is of type character varying[] but >>>>> expression is of type text >>>>> LINE 1: INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'; >>>>> ^ >>>>> HINT: You will need to rewrite or cast the expression. >>>>> >>>>> It's easy enough to add a cast, but I was curious if this was expected >>>>> and desired behavior. Thanks. >>>>> >>>>> Ken >>>>> >>>>> -- >>>>> AGENCY Software >>>>> A Free Software data system >>>>> By and for non-profits >>>>> *http://agency-software.org/ <http://agency-software.org/>* >>>>> *https://agency-software.org/demo/client >>>>> <https://agency-software.org/demo/client>* >>>>> ken.tan...@agency-software.org >>>>> (253) 245-3801 >>>>> >>>>> Subscribe to the mailing list >>>>> <agency-general-requ...@lists.sourceforge.net?body=subscribe> to >>>>> learn more about AGENCY or >>>>> follow the discussion. >>>>> >>>> >>>> >>> >>> >>> -- >>> AGENCY Software >>> A Free Software data system >>> By and for non-profits >>> *http://agency-software.org/ <http://agency-software.org/>* >>> *https://agency-software.org/demo/client >>> <https://agency-software.org/demo/client>* >>> ken.tan...@agency-software.org >>> (253) 245-3801 >>> >>> Subscribe to the mailing list >>> <agency-general-requ...@lists.sourceforge.net?body=subscribe> to >>> learn more about AGENCY or >>> follow the discussion. >>> >> >> > > > -- > AGENCY Software > A Free Software data system > By and for non-profits > *http://agency-software.org/ <http://agency-software.org/>* > *https://agency-software.org/demo/client > <https://agency-software.org/demo/client>* > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing list > <agency-general-requ...@lists.sourceforge.net?body=subscribe> to > learn more about AGENCY or > follow the discussion. >
