By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,775 Members | 1,740 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,775 IT Pros & Developers. It's quick & easy.

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

P: n/a
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*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
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 ma*******@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: 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

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
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: te***@esc1.com

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

Nov 23 '05 #5

P: n/a

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

Nov 23 '05 #6

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

P: n/a
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/

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

Nov 23 '05 #8

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #9

P: n/a

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

Nov 23 '05 #10

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #11

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #12

P: n/a
>> 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? <<

I don't know if there is anything special about PostgreSQL, but the
Standard syntax is "<datatype> [DEFAULT <constant>] [NOT NULL]" -
picky, picky, picky.

The best design practice is to start with the "DEFAULT <constant> NOT
NULL" if there is an actual default value. Avoid NULLs in encoding
schemes whenever possible, but do not be overly afraid of them. Allow
NULLs whenthe column is a measurement on scale.

I have a few chapters on this in DATA & DATABASES.
Nov 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.