Postgres Pro
Downloads
Home   >   mailing lists

Re: Array string casts with SELECT but not SELECT DISTINCT - Mailing list pgsql-general

From sridhar bamandlapally
Subject Re: Array string casts with SELECT but not SELECT DISTINCT
Date
Msg-id CAGuFTBUwK-k1d8iV2pvcc4v=dktL8scTPF2_RiiJu419R9=X8Q@mail.gmail.com
Whole thread Raw
In response to Array string casts with SELECT but not SELECT DISTINCT  (Ken Tanzer <ken.tanzer@gmail.com>)
Responses Re: Array string casts with SELECT but not SELECT DISTINCT  (David G Johnston <david.g.johnston@gmail.com>)
Re: Array string casts with SELECT but not SELECT DISTINCT  (Ken Tanzer <ken.tanzer@gmail.com>)
List pgsql-general
Tree view
>>>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@gmail.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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Michael_LT
Date:
Subject: how to convert "output deleted/inserted into" in My SQL to Postgres
Next
From: David G Johnston
Date:
Subject: Re: Array string casts with SELECT but not SELECT DISTINCT