Re: A couple of newbie questions ...

Shane Ambler wrote:

>> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')

> To be honest I hadn't seen the use of INSERT INTO table (fld_x,
> fld_y,fld_z) VALUES (DEFAULT, 'y','z') before, I have always gone with
> INSERT INTO table (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')

... which is not the same thing.

> is DEFAULT a better option than using NULL? or is it just a preference
> to spell out the implied default entry?

It's completely different - you're running into bad habits developed
from using MySQL in non-ANSI-compliant mode (though some of these might
also apply in strict mode). See below.

In PostgreSQL, like most databases, inserting NULL will in fact insert a
NULL value for that field. Using DEFAULT tells the database to pick the
default value for the field, or if unspecified insert NULL for that
field. You can't just use NULL when you mean DEFAULT.

With this schema:

CREATE TABLE t (
fld_x SERIAL PRIMARY KEY,
fld_y VARCHAR(255),
fld_z VARCHAR(255)
);

which actually behaves like:

CREATE SERIAL t_id_seq;
CREATE TABLE t (
fld_x INTEGER NOT NULL DEFAULT nextval('t_id_seq'),
fld_y VARCHAR(255),
fld_z VARCHAR(255),
PRIMARY KEY(fld_x)
);

this statement:

INSERT INTO t (fld_x, fld_y,fld_z) VALUES (NULL, 'y','z')

will fail with:

ERROR: null value in column "fld_x" violates not-null constraint

because NULL isn't valid in a PRIMARY KEY field.

On the other hand, if you write this:

INSERT INTO t (fld_x, fld_y,fld_z) VALUES (DEFAULT, 'y','z')

it'll succeed, because the DEFAULT will be evaluated as
nextval('t_id_seq') so it'll get the next value from the sequence from
the SERIAL primary key.

MySQL-isms:

'' is not the same as NULL. NULL essentially means "unknown/undefined",
whereas '' means a specific and known value, a zero-length string. They
mean different things, and will also compare non-equal.

In INSERT, NULL is not the same as DEFAULT. NULL means NULL. DEFAULT
means "evaluate the expression in the DEFAULT clause for this field in
the schema definition, or if none is specified use NULL".

NULL is not equal to NULL. The result of evaluating the expression:
NULL = NULL
is actually NULL, not true. If you want to test for nullity use IS NULL
and IS NOT NULL instead. If you really want NULL to compare as equal to
NULL and unequal to other values (instead of NULL when compared to other
values) use IS DISTINCT FROM. See the documentation for more details.

Note that if you really, really, really want to emulate auto_increment
from MySQL, you can do so with a trigger that replaces NULL values in a
given field with values selected from a counter table. Concurrency will
be very poor, though, as will performance in general, and it's a much
better idea to just use a proper sequence.

--
Craig Ringer