Re: Array string casts with SELECT but not SELECT DISTINCT

[sridhar bamandlapally <sridhar.bn1@xxxxxxxxx>]

>>>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.tanzer@xxxxxxxxx> 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/

https://agency-software.org/demo/client

ken.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.