Обсуждение: Null value representation in the array data structure

Dear all,

I'd like to store arrays of numerical data as a column
of data. Everything works fine in PostgreSQL 7.1.3
except for one thing: there seems to be no way of
representing NULL numerical data. For example, using
the following table

    create table arraydata (rowid text primary key, data float4[]);

the statement

    insert into arraydata values ( 'rowname', '{100,NULL,300}' );

returns "ERROR:  Bad float4 input format 'NULL'" while

    insert into arraydata values ( 'rowname', '{100,,300}' );

successfully inserts but inserts '{100, 0, 300}'.
Because 0 is for me a legitimate data value, one not to
be confused with NULL, this does not work for me
either.

QUESTIONS:

1. Is there a way of representing NULL numerical data
in arrays I have overlooked?

2. If not, are there workarounds more attractive than
using a numerical flag value (e.g., -9999)? (That
reminds me of FORTRAN 77 days long ago!)

Paul Laub




Paul B. Laub      http://astatine.incyte.com/laub      (650) 845-5411 (voice)
Incyte Genomics, Inc.   3160 Porter Dr.  Palo Alto, CA 94304 plaub@incyte.com
                       *** Incite genomics! ***

"Paul Laub" <plaub@incyte.com> writes:
> 1. Is there a way of representing NULL numerical data
> in arrays I have overlooked?

Arrays don't cope with NULL elements at all (numerical or otherwise).
Fixing this is on the TODO list.

> 2. If not, are there workarounds more attractive than
> using a numerical flag value (e.g., -9999)? (That
> reminds me of FORTRAN 77 days long ago!)

Consider whether you really need an array at all.  A lot of people
regard arrays as faintly raffish because they don't fit into the
relational worldview very well.  Can you break the data down into
tables instead?

            regards, tom lane