Обсуждение: populating arrays with default values

I come from a long background with C and isam. Moving into php and
postgresql is therefore not all that strange, but I suffer with the
comparative lack of structure at times.

I deal with a lot of psych scoring and am establishing tables which will
contain the response patterns for each individual test within a battery.
In defining this, I plan on something like: ans CHAR(1) ARRAY[192]

But I learned that PostgreSQL does not 'enforce' this. I would rather
have an '?' placed in each of the 192 array elements (and thereby
constrain with NOT NULL) to indicate lack of a response (the default)
when the test records are first established for the client.

Because the client does not need to complete a test at one sitting, but
must do so before tackling another test in his or her battery, I need to
'see' where things stand test by test at each login.

I can certainly write php script to fill the array as each record is
established - clumsy the first time around, but pretty efficient after
that - just an include function. There is apparently no other way of
defining (inserting) a default value for array elements.

Am I correct in my approach to this?

Thanks for any insight .....

George

On Wednesday, November 14, 2007 12:09 PM -0800 "G. J. Walsh"
<gjwalsh@dscdirectionalservices.com> wrote:

> I come from a long background with C and isam. Moving into php and
> postgresql is therefore not all that strange, but I suffer with the
> comparative lack of structure at times.

Same here. I'm a power C++ guy but relatively new at SQL. I can read it but
not yet write it fluently.

> I deal with a lot of psych scoring and am establishing tables which will
> contain the response patterns for each individual test within a battery.
> In defining this, I plan on something like: ans CHAR(1) ARRAY[192]
>
> But I learned that PostgreSQL does not 'enforce' this. I would rather
> have an '?' placed in each of the 192 array elements (and thereby
> constrain with NOT NULL) to indicate lack of a response (the default)
> when the test records are first established for the client.

Instead of a char, could you use an enumeration? Are there in fact 256
distinct answers for each array position? (I'm not certain that SQL, or the
PostgreSQL dialect, have such a thing.)

On Nov 14, 2007, at 8:30 PM, Kenneth Porter wrote:
> On Wednesday, November 14, 2007 12:09 PM -0800 "G. J. Walsh"
> <gjwalsh@dscdirectionalservices.com> wrote:
>
>> I come from a long background with C and isam. Moving into php and
>> postgresql is therefore not all that strange, but I suffer with the
>> comparative lack of structure at times.
>
> Same here. I'm a power C++ guy but relatively new at SQL. I can
> read it but not yet write it fluently.

My advice: remember that SQL is a set definition language. Always
think in terms of sets/groups of data, and not in terms of a series
of operations you need to perform.

>> I deal with a lot of psych scoring and am establishing tables
>> which will
>> contain the response patterns for each individual test within a
>> battery.
>> In defining this, I plan on something like: ans CHAR(1) ARRAY[192]
>>
>> But I learned that PostgreSQL does not 'enforce' this. I would rather
>> have an '?' placed in each of the 192 array elements (and thereby
>> constrain with NOT NULL) to indicate lack of a response (the default)
>> when the test records are first established for the client.
>
> Instead of a char, could you use an enumeration? Are there in fact
> 256 distinct answers for each array position? (I'm not certain that
> SQL, or the PostgreSQL dialect, have such a thing.)

IIRC, in 8.3 elements within an array can be NULL, would would be
what you're looking for. ('?' is just a not-so-good way of
representing "I don't know", which is why NULL is for. :) )
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Please reply-all so everyone can participate. And top-posting == bad ;)

On Mon, Nov 19, 2007 at 09:39:18AM -0800, G. J. Walsh wrote:
> Thanks for the comment.
>
> I am currently using 8.2 - 8.3 is not yet available on Mandriva Cooker.
>
> All I am needing to do is manage a fixed character length which would
> hold a prechecked response for a stated number of items. Since then I
> have experimented with throwing that 'string' back and forth, managing
> it with PHP which would be necessary in any case, the responses
> originating within a form. In effect, I am now managing a character type
> of given length rather than an array. The '?' was simply a marked filler
> indicating a lack of response for that item. The only overhead I see is
> the need to 'fill' the array when a record (row) is initiated and that
> is pretty straightforward.
>
> Your suggestion is to set up an array 'any' filled with nulls. If that
> is what you suggest I do with 3.5, it might be worth it to download
> source for PostgreSQL and give it a try before I 'freeze' the table
> design and get into production. However, it seems to me that there is
> little to be gained in overall effectiveness between the two approaches
> in this particular application.
>
> By all means correct me if I am wrong about this.

Technically, an array with NULL values for unanswered questions would be
more correct... but if every answer is a single character, that doesn't
really buy you a lot over just using a string. It might actually have a
much larger overhead, too... I suspect that every element in the array
will have a varlena header, which would cost you an extra 1-4 bytes.

> On Sun, 2007-11-18 at 22:31 -0600, Decibel! wrote:
> > On Nov 14, 2007, at 8:30 PM, Kenneth Porter wrote:
> > > On Wednesday, November 14, 2007 12:09 PM -0800 "G. J. Walsh"
> > > <gjwalsh@dscdirectionalservices.com> wrote:
> > >
>
> > >> In defining this, I plan on something like: ans CHAR(1) ARRAY[192]
> > >>
> > >> But I learned that PostgreSQL does not 'enforce' this. I would rather
> > >> have an '?' placed in each of the 192 array elements (and thereby
> > >> constrain with NOT NULL) to indicate lack of a response (the default)
> > >> when the test records are first established for the client.
> > >
>
> > IIRC, in 8.3 elements within an array can be NULL, would would be
> > what you're looking for. ('?' is just a not-so-good way of
> > representing "I don't know", which is why NULL is for. :) )
>

--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828