Arrays: missing/skipped elements

Started by Steve Crawfordover 23 years ago2 messagesgeneral
Jump to latest
#1Steve Crawford
scrawford@pinpointresearch.com

I know from the docs that array elements can't have a null value. It appears,
therefore, that Postgres fills in missing elements with zero-length strings,
the int or numeric value zero, etc. appropriate to the data type. Is my
interpretation correct and is this documented/likely to continue in future
versions?

My test (extraneous lines removed):

create table foo (bar text[]);
insert into foo values ('{"a","b","c"}');
insert into foo values ('{,,,"4"}');

select * from foo;
{a,b,c}
{"","","",4}

select '-' || bar[1] || '-' from foo;
-a-
--

select count(*) from foo where bar[1] = '';
1

Cheers,
Steve

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Steve Crawford (#1)
Re: Arrays: missing/skipped elements

Steve Crawford <scrawford@pinpointresearch.com> writes:

I know from the docs that array elements can't have a null value. It appears,
therefore, that Postgres fills in missing elements with zero-length strings,
the int or numeric value zero, etc. appropriate to the data type. Is my
interpretation correct and is this documented/likely to continue in future
versions?

One would hope that we'll support null elements in arrays, sooner or
later. I'm not really sure what the textual representation for that
should be, however.

regards, tom lane