Re: Null records inserted

On Sat, Mar 24, 2001 at 01:07:08PM +1030, Stephen Davies wrote:
> Hello.
>
> I have a PostgreSQL (V7.0.2) database with several tables defined with
> a primary key set by default to a sequence value.
>
> 99% of the time this works as expected but there is a trickle of
> records appearing in each table where every field is null.
>
> Could somebody please explain to me how this is possible so that I
> might be able to track down the programming error that causes them.
>
> That is, how can a field that is defined as having a default value wind
> up in the database as null.
>
> Despite the usual rules regarding null processing, I would still expect
> a second unique primary key value of null to be rejected.
>
> The database is usually maintained via a VB5 ODBC application.

Question, is the primary key a multicolumn key? If so, you need to have
NOT NULL constraints on each of the columns. Otherwise, I don't see how
an integer primary key could ever get a NULL value (bug?).

Try this:

create sequence foo_id_seq;

create table foo (id integer DEFAULT nextval('foo_id_seq'), name text);

insert into foo (name) values ('bar');

update foo set id = NULL, name = NULL where id = currval('foo_id_seq');

Now you should have a record with all NULL values. If however, "id" is
defined with a PRIMARY KEY constraint (or as SERIAL "datatype") the
update should fail.

--
Eric G. Miller <egm2(at)jps(dot)net>