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
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.
