Re: clarification about ARRAY constructor implementation

On Nov 11, 2011, at 8:38, the6campbells <the6campbells(at)gmail(dot)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. Null is 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 be used 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 the target'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.