Re: Array string casts with SELECT but not SELECT DISTINCT

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.
>