Re: Array string casts with SELECT but not SELECT DISTINCT

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