Search Postgresql Archives

Re: Array string casts with SELECT but not SELECT DISTINCT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Currently, this we are running in production, not faced any issues with functional or performance or database maintenance, I am talking about banking related application

As per my knowledge/experience this should work without any downside, 

and, this cast creation method is part of postgresql document from 8.4

url: http://www.postgresql.org/docs/9.4/static/sql-createcast.html

Thanks
Sridhar BN



On Sun, Feb 22, 2015 at 7:39 AM, Ken Tanzer <ken.tanzer@xxxxxxxxx> wrote:
I tried that and it does indeed work. (With, of course, the appropriate permissions to create the cast.)

So this makes me wonder--is there any downside or unwelcome side effects to having such a cast?  And if not, why isn't it part of the default setup?

Cheers,
Ken

On Sat, Feb 21, 2015 at 3:34 AM, sridhar bamandlapally <sridhar.bn1@xxxxxxxxx> wrote:
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.tanzer@xxxxxxxxx> 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..."

Cheers,
Ken

On Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <sridhar.bn1@xxxxxxxxx> 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.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
 
--
learn more about AGENCY or
follow the discussion.




--
learn more about AGENCY or
follow the discussion.




--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux