Обсуждение: Null records inserted

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.

Cheers and thanks,
Stephen Davies

========================================================================
Stephen Davies Consulting                              scldad@sdc.com.au
Adelaide, South Australia.                             Voice: 08-8177 1595
Computing & Network solutions.                         Fax: 08-8177 0133

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@jps.net>

Stephen Davies <scldad@sdc.com.au> writes:
> That is, how can a field that is defined as having a default value wind
> up in the database as null.

Via an explicit specification of a NULL field value in an INSERT.
A default value does not override an explicit specification.

> Despite the usual rules regarding null processing, I would still expect
> a second unique primary key value of null to be rejected.

If you had actually declared it as a primary key (which implies NOT
NULL) then even one null would be disallowed.  However a unique
constraint without NOT NULL does not disallow nulls, even multiple ones.
There's been some discussion about whether that's the correct behavior,
but that's how it works at the moment.

            regards, tom lane

Thanks Tom.

I'm not sure how an explicit null can be being specified but it sounds
like a plausible explanation. Now off to battle with VB;-(

Cheers,
Stephen Davies

On Fri, 23 Mar 2001 22:33:47 -0500, Tom Lane said:
> Stephen Davies <scldad@sdc.com.au> writes:
> > That is, how can a field that is defined as having a default value wind
> > up in the database as null.
>
> Via an explicit specification of a NULL field value in an INSERT.
> A default value does not override an explicit specification.
>
> > Despite the usual rules regarding null processing, I would still expect
> > a second unique primary key value of null to be rejected.
>
> If you had actually declared it as a primary key (which implies NOT
> NULL) then even one null would be disallowed.  However a unique
> constraint without NOT NULL does not disallow nulls, even multiple ones.
> There's been some discussion about whether that's the correct behavior,
> but that's how it works at the moment.
>
>             regards, tom lane


========================================================================
Stephen Davies Consulting                              scldad@sdc.com.au
Adelaide, South Australia.                             Voice: 08-8177 1595
Computing & Network solutions.                         Fax: 08-8177 0133