Обсуждение: Array string casts with SELECT but not SELECT DISTINCT
Hi. Here's a boiled down example of something that caught me by surprise:
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
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
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Ken Tanzer wrote > 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. The select resolves the distinct by converting the unknown into a text so when it gets to the insert it is already typed in the incompatible type. Without distinct the select leaves the value as an unknown and then passing it to the insert coerces it to the expected array. It's a bottom-up evaluation plan instead of top-down one. Both have merit but it definitely seems easier to implement the bottom-up version and coerce only when needed with the immediately available information instead of trying to skip around between layers. David J. -- View this message in context: http://postgresql.nabble.com/Array-string-casts-with-SELECT-but-not-SELECT-DISTINCT-tp5838663p5838667.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>>>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 TABLEag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1ag_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 textLINE 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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
>>>ERROR: column "my_array" is of type character varying[] but expression is of type textplease try this below, may be this should helpCREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;just for info:actually this should be available in default
What is the point of doing this? Did you do this and then run the OP's query and see if it solved the problem? Do you understand the "type[]" means "array version of type"?
David J.
View this message in context: Re: Array string casts with SELECT but not SELECT DISTINCT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
"If DISTINCT is specified, all duplicate rows are removed from the result set..."
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.
Cheers,
Ken
On Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>>>ERROR: column "my_array" is of type character varying[] but expression is of type textplease try this below, may be this should helpCREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;just for info:actually this should be available in defaultOn 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 TABLEag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1ag_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 textLINE 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 SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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@gmail.com> 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 existsOf 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,KenOn Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:>>>ERROR: column "my_array" is of type character varying[] but expression is of type textplease try this below, may be this should helpCREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;just for info:actually this should be available in defaultOn 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 TABLEag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1ag_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 textLINE 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 SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
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@gmail.com> wrote:
HiPlease see below, this works, way for implicit type castingbns=# CREATE TEMP TABLE foo (my_array varchar[]);CREATE TABLEbns=#bns=# INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1bns=#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 textLINE 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 CASTbns=#bns=# INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'; ---- this worksINSERT 0 1bns=#bns=#in previous mail, sorry for not mentioning varchar"[]"We did type cast implicit method to avoid application code changes for Oracle to PostgreSQL compatibleThanksSridhar BNOn Sat, Feb 21, 2015 at 9:38 AM, Ken Tanzer <ken.tanzer@gmail.com> 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 existsOf 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,KenOn Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:>>>ERROR: column "my_array" is of type character varying[] but expression is of type textplease try this below, may be this should helpCREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;just for info:actually this should be available in defaultOn 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 TABLEag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1ag_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 textLINE 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 SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.--AGENCY SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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
Thanks
Sridhar BN
On Sun, Feb 22, 2015 at 7:39 AM, Ken Tanzer <ken.tanzer@gmail.com> 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,KenOn Sat, Feb 21, 2015 at 3:34 AM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:HiPlease see below, this works, way for implicit type castingbns=# CREATE TEMP TABLE foo (my_array varchar[]);CREATE TABLEbns=#bns=# INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1bns=#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 textLINE 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 CASTbns=#bns=# INSERT INTO foo (my_array) SELECT DISTINCT '{TEST}'; ---- this worksINSERT 0 1bns=#bns=#in previous mail, sorry for not mentioning varchar"[]"We did type cast implicit method to avoid application code changes for Oracle to PostgreSQL compatibleThanksSridhar BNOn Sat, Feb 21, 2015 at 9:38 AM, Ken Tanzer <ken.tanzer@gmail.com> 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 existsOf 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,KenOn Fri, Feb 20, 2015 at 6:30 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:>>>ERROR: column "my_array" is of type character varying[] but expression is of type textplease try this below, may be this should helpCREATE CAST (text AS varchar) WITH INOUT AS IMPLICIT;just for info:actually this should be available in defaultOn 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 TABLEag_reach_test=> INSERT INTO foo (my_array) SELECT '{TEST}';INSERT 0 1ag_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 textLINE 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 SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.--AGENCY SoftwareA Free Software data systemBy and for non-profitslearn more about AGENCY orfollow the discussion.--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
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?
By and large implicit casts work great if you code everything without error. When they become a problem is when you make mistakes and because of implicit casting instead of getting an error you get bogus results.
David J.
View this message in context: Re: Array string casts with SELECT but not SELECT DISTINCT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.