clarification about ARRAY constructor implementation

From: the6campbells <the6campbells(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: clarification about ARRAY constructor implementation
Date: 2011-11-11 13:38:37
Message-ID: CAFEjsq4OmR31PyR+dJDu0H9QCuhokG+P8eBhJLWqXJygKZSXVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Johnston 2011-11-11 13:59:31 Re: clarification about ARRAY constructor implementation
Previous Message Thomas Kellerer 2011-11-11 08:07:40 Re: ORACLE PROCEDURE TO POSTGRES FUNCTION -ERROR IN THE OVER PART