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

[Miles Keaton]

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.}

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Peter Eisentraut]

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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Terry Lee Tucker]

Why would you never define a column as NOT NULL, or am I misunderstandin g 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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

--

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

---------------------------(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

[David Garamond]

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 misunderstandin g 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

[Terry Lee Tucker]

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 misunderstandin g
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: te***@esc1.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Michael Glaesemann]

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_birth dates (
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[David Garamond]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Peter Eisentraut]

Am Freitag, 8. Oktober 2004 12:20 schrieb Terry Lee Tucker:

Why would you never define a column as NOT NULL, or am I misunderstandin g
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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[David Garamond]

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_birth dates (
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Michael Glaesemann]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend