Re: Array string casts with SELECT but not SELECT DISTINCT

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.