Re: Array string casts with SELECT but not SELECT DISTINCT

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(dot)tanzer(at)gmail(dot)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(dot)bn1(at)gmail(dot)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(dot)tanzer(at)gmail(dot)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(dot)bn1(at)gmail(dot)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(dot)tanzer(at)gmail(dot)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(dot)tanzer(at)agency-software(dot)org
>>>>> (253) 245-3801
>>>>>
>>>>> Subscribe to the mailing list
>>>>> <agency-general-request(at)lists(dot)sourceforge(dot)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(dot)tanzer(at)agency-software(dot)org
>>> (253) 245-3801
>>>
>>> Subscribe to the mailing list
>>> <agency-general-request(at)lists(dot)sourceforge(dot)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(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list
> <agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
>