Re: Array string casts with SELECT but not SELECT DISTINCT

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.