Re: Array string casts with SELECT but not SELECT DISTINCT

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.