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