Thread: clarification about ARRAY constructor implementation

On Nov 11, 2011, at 8:38, the6campbells <the6campbells@gmail.com> wrote:

> consider the following
>
> create table TARRBINT ( RNUM integer  not null , CARRBINT bigint array[5] ) ;
>
> Can someone clarify why Postgres does not like examples 2, 6 and 7

An array must have a base type; an empty array has nothing with which to infer a type so you must cast it yourself.
Nullis typeless and so you need an explicit cast if all values are null. 

>
> 1.insert into TARRBINT(RNUM, CARRBINT) values ( 0, null);
> 2.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]);
> 3.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[]::integer[]);
> 4.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,1]);
> 5.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[1, null]);
> 6.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null]);
> 7.insert into TARRBINT(RNUM, CARRBINT) values ( 0, ARRAY[null,null]);
>
> Similarly, why does it seem to force casting expressions with other instances of literal values used in the array
constructor.Is this due to Postgres treating some literal values to be a 'lob' type and thus concluding that it cannot
beused in the context of array constructor? 
>
> For example, this will work
>
> create table TARRCHAR ( RNUM integer  not null , CARRCHAR char(72) array[5]   ) ;
> insert into TARRCHAR(RNUM, CARRCHAR) values ( 1, ARRAY ['<world>'])
>
> But scenarios like this will not
>
> create table TXML ( RNUM integer  not null , CXML xml   ) ;
> insert into TXML(RNUM, CXML) values ( 1, '<world></world>');
>
> create table TARRXML ( RNUM integer  not null , CARRXML xml array[5]   ) ;
> insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['<world></world>']);
>
> ERROR:  column "carrxml" is of type xml[] but expression is of type text[]
> LINE 1: insert into TARRXML(RNUM, CARRXML) values ( 1, ARRAY ['<worl...
>                                                        ^
> HINT:  You will need to rewrite or cast the expression.

Going by observation/inference here...

An array can never take on the "unknown" type whereas a scalar can.  The unknown type can be passed to the Insert where
thetarget's column type can then be used for casting.  The array, needing immediate casting, chooses the most liberal
type,in this case text, before it gets sent to the Insert. 

David J.