Postgres Pro
Downloads
Home   >   mailing lists

Thread: Effects of not using NOT NULL

Effects of not using NOT NULL

From
"Matthew V." <
Date:
In DB2, when tables are created without using NOT NULL on columns, an
extra byte is added to each record in the column as a NULL indicator flag.
 This byte is part of the data, and it exists for every occurrence of that
field.  (I think it's a byte--it might be two).  This has the tendency to
add a lot of space to the tablespace for maintaining the NULL indicator,
and also imposes a requirement on embedded SQL applications of using a
NULL indicator field when selecting/updating such fields.  E.g.:

    EXEC SQL
        SELECT nullableField
        INTO   :variabe:nullIndicator
        FROM   table
    END-EXEC.

where nullIndicator is defined as a binary field (short, PIC S9(04) COMP,
etc) in the host language.  A seperate variable must be used for each
nullable field being selected.

Does Postgresql include a similar NULL indicator flag on nullable fields,
and if so, how big is it?  Also, does that impose the restriction on
embedded SQL of having a variable in which to store the null indicator
flag?

--
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...

Re: Effects of not using NOT NULL

From
Bruce Momjian
Date:
PostgreSQL uses a bitmask to track NULL values.  There is a bit for
every column in the row _if_ there are any nulls in the row, at least in
7.3beta.  It does require a null indicator in client applications.

---------------------------------------------------------------------------

"Matthew V." < wrote:
> In DB2, when tables are created without using NOT NULL on columns, an
> extra byte is added to each record in the column as a NULL indicator flag.
>  This byte is part of the data, and it exists for every occurrence of that
> field.  (I think it's a byte--it might be two).  This has the tendency to
> add a lot of space to the tablespace for maintaining the NULL indicator,
> and also imposes a requirement on embedded SQL applications of using a
> NULL indicator field when selecting/updating such fields.  E.g.:
>
>     EXEC SQL
>         SELECT nullableField
>         INTO   :variabe:nullIndicator
>         FROM   table
>     END-EXEC.
>
> where nullIndicator is defined as a binary field (short, PIC S9(04) COMP,
> etc) in the host language.  A seperate variable must be used for each
> nullable field being selected.
>
> Does Postgresql include a similar NULL indicator flag on nullable fields,
> and if so, how big is it?  Also, does that impose the restriction on
> embedded SQL of having a variable in which to store the null indicator
> flag?
>
> --
> Matthew Vanecek
> perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'

Nice .sig:

  $ perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
  RTFM

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073