Thread: when to use NULL and when to NOT NULL DEFAULT ''

PG peeps:

What's the prevailing wisdom & best-practice advice about when to let
a varchar (or any) column be NULL, and when to make it NOT NULL
DEFAULT '' (or '0000-00-00' or whatever) - in PostgreSQL?





{Moving to PG from MySQL where we were always advised to use NOT NULL
to save a byte or something.  But that was years ago so sorry for the
stupid-sounding question, but I had to ask.}

Am Freitag, 8. Oktober 2004 07:22 schrieb Miles Keaton:
> What's the prevailing wisdom & best-practice advice about when to let
> a varchar (or any) column be NULL, and when to make it NOT NULL
> DEFAULT '' (or '0000-00-00' or whatever) - in PostgreSQL?

Briefly, you always do the first and never do the second.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Why would you never define a column as NOT NULL, or am I misunderstanding what
you are saying?

On Friday 08 October 2004 06:07 am, Peter Eisentraut saith:
>
> Briefly, you always do the first and never do the second.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Btw, MySQL manual used to recommend (or still does?) defining all
columns as NOT NULL as much as possible, "because NULL is slow"... :-)

For me it's pretty obvious, if you are never going to allow the column
to have an "unknown value", then define it NOT NULL to let the database
guarantee that. Otherwise, nullable it is.



Terry Lee Tucker wrote:
> Why would you never define a column as NOT NULL, or am I misunderstanding what
> you are saying?
>
> On Friday 08 October 2004 06:07 am, Peter Eisentraut saith:
>
>>Briefly, you always do the first and never do the second.

--
dave

Peter Eisentraut wrote:
> Am Freitag, 8. Oktober 2004 07:22 schrieb Miles Keaton:
>
>>What's the prevailing wisdom & best-practice advice about when to let
>>a varchar (or any) column be NULL, and when to make it NOT NULL
>>DEFAULT '' (or '0000-00-00' or whatever) - in PostgreSQL?
>
> Briefly, you always do the first and never do the second.

Speaking of NULLs, what does the relational model ideal suggest for
missing information?

a) no NULL at all;
b) NULL and N/A;

I've read both, a) in "handling missing information without NULLs"
articles and b) in Joe Celko's book ("Codd proposed two kind of missing
information: NULL for unknown and N/A for not applicable").

--
dave

Understood. We use NOT NULL as you have stated below. I just wanted to make
sure we weren't doing something stupid. Thanks for the reply...

On Friday 08 October 2004 07:09 am, David Garamond saith:
> Btw, MySQL manual used to recommend (or still does?) defining all
> columns as NOT NULL as much as possible, "because NULL is slow"... :-)
>
> For me it's pretty obvious, if you are never going to allow the column
> to have an "unknown value", then define it NOT NULL to let the database
> guarantee that. Otherwise, nullable it is.
>
> Terry Lee Tucker wrote:
> > Why would you never define a column as NOT NULL, or am I misunderstanding
> > what you are saying?
> >
> > On Friday 08 October 2004 06:07 am, Peter Eisentraut saith:
> >>Briefly, you always do the first and never do the second.
>
> --
> dave
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match

--
Quote: 78
"We have to keep in mind we are a nation under God, and if we ever
 forget that, we'll be just a nation under."

 --Ronald Reagan

 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

On Oct 8, 2004, at 8:12 PM, David Garamond wrote:
> Speaking of NULLs, what does the relational model ideal suggest for
> missing information?
>
> a) no NULL at all;
> b) NULL and N/A;

I've read both of those as well. Date has a pretty good section
regarding NULLs in his Introduction to Database Systems. The upshot is
you shouldn't use NULL. Either your domain (data type) should include
values to indicate N/A (and all other values, as needed), or make an
additional relation referencing the first, giving values for the keys
you *do* know. For example;

CREATE TABLE employees (
    emp_id serial not null unique
    , emp_name text not null
    , birthdate date
);

For employees you don't have birthdates for, you could use NULL in SQL.
However, as relationally one shouldn't use NULL, you would do the
following:

CREATE TABLE employees (
    emp_id SERIAL NOT NULL UNIQUE
    , emp_name TEXT NOT NULL
);

CREATE TABLE employees_birthdates (
    emp_id INTEGER NOT NULL REFERENCES employees (emp_id)
    , birthdate DATE NOT NULL
);

In any case, one would never use NULL. Either the domain includes a
value for all possible values (including N/A) or you set up the db
schema appropriately.

Cheers,

Michael Glaesemann
grzm myrealbox com

Am Freitag, 8. Oktober 2004 12:20 schrieb Terry Lee Tucker:
> Why would you never define a column as NOT NULL, or am I misunderstanding
> what you are saying?

His question was, should one use null values or should one use artificially
reserved real data values to indicate missing values, as MySQL appears to
have recommended at some point.  My suggestion was to use null values.

Of course, if you don't want null values, you declare your column accordingly.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Michael Glaesemann wrote:
> For employees you don't have birthdates for, you could use NULL in SQL.
> However, as relationally one shouldn't use NULL, you would do the
> following:
>
> CREATE TABLE employees (
>     emp_id SERIAL NOT NULL UNIQUE
>     , emp_name TEXT NOT NULL
> );
>
> CREATE TABLE employees_birthdates (
>     emp_id INTEGER NOT NULL REFERENCES employees (emp_id)
>     , birthdate DATE NOT NULL
> );
>
> In any case, one would never use NULL. Either the domain includes a
> value for all possible values (including N/A) or you set up the db
> schema appropriately.

Hm, that can be painful. What if I have ten optional attributes;
separate them to ten different tables?

--
dave

On Oct 8, 2004, at 10:19 PM, David Garamond wrote:

> Michael Glaesemann wrote:
>> In any case, one would never use NULL. Either the domain includes a
>> value for all possible values (including N/A) or you set up the db
>> schema appropriately.
>
> Hm, that can be painful. What if I have ten optional attributes;
> separate them to ten different tables?

Strictly? That's how I understand it. Whether or not it's performant
under PostgreSQLis another matter. :)

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:
>>> In any case, one would never use NULL. Either the domain includes a
>>> value for all possible values (including N/A) or you set up the db
>>> schema appropriately.
>>
>> Hm, that can be painful. What if I have ten optional attributes;
>> separate them to ten different tables?
>
> Strictly? That's how I understand it. Whether or not it's performant
> under PostgreSQLis another matter. :)

The relationists' SQL replacement had better have more convenient JOIN
syntax then. :-)

--
dave

On Oct 8, 2004, at 11:11 PM, David Garamond wrote:

> The relationists' SQL replacement had better have more convenient JOIN
> syntax then. :-)

Personally I find the JOIN ... USING syntax quite convenient. More
robust domain support is another side to this issue.

Cheers,

Michael Glaesemann
grzm myrealbox com