Обсуждение: Bug #851: cast() no longer works in array inserts

Malcolm Tredinnick (malcolm@commsecure.com.au) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
cast() no longer works in array inserts

Long Description
If I have a table containing an array (e.g. a bigint[] field), then it does not appear to be possible to insert values
usingthe cast() function. This used to work in PostgreSQL 7.1, but no longer works in 7.2 or 7.3. I cannot see anything
inthe release notes saying the previous behaviour was considered incorrect and the documentation seems to indicate it
shouldbe possible. 

If this is not meant to work, then this is a documentation bug, because it is unclear how to insert a constant like 0
intoa bigint field in an array. 

The example code below works in 7.1, but not in later versions. The error message given is

   ERROR:  Bad int8 external representation "{0::bigint, 0::bigint}"

for the first example and similarly for the second (with the appropriate cast statements substituted).


Sample Code
create table foo (bar bigint[]);

insert into foo values ('{"0"::bigint, "0"::bigint}');

insert into foo values ('{cast("0" as decimal(12)), cast("0" as decimal(12))}');


No file was uploaded with this report

pgsql-bugs@postgresql.org writes:
> cast() no longer works in array inserts

It never has.  You are confusing a rather lame failure of error checking
in 7.1 with a feature.  7.1 and before will in fact take quite a lot of
random garbage in array input strings :=(

            regards, tom lane

Malcolm Tredinnick <malcolm@commsecure.com.au> writes:
> So given that one cannot just insert 0 into a bigint field (even an
> array of bigints), since postgres complains it is not of the correct
> type, how is one supposed to fille a field of bigint[] with {0, 0}?

I must be missing something ... what is wrong with

regression=# create table foo (bar bigint[]);
CREATE TABLE
regression=# insert into foo values ('{0, 0}');
INSERT 1101788 1
regression=# select * from foo;
  bar
-------
 {0,0}
(1 row)

            regards, tom lane