Thread: Serial and NULL values
I just received a message from someone complaining about SERIAL/sequence. I think there is a problem:test=> create table test (x int, y serial);NOTICE: CREATE TABLE will create implicit sequence 'test_y_seq'for SERIAL column 'test.y'NOTICE: CREATE TABLE/UNIQUE will create implicit index 'test_y_key' for table 'test'CREATEtest=>insert into test (x) values (100);INSERT 19359 1test=> insert into test (x) values (100);INSERT 19360 1 These work fine, but why does this fail: test=> insert into test values (100, null);ERROR: ExecAppend: Fail to add null value in not null attribute ytest=> insertinto test values (100, 0);INSERT 19363 1test=> insert into test values (100, 0);ERROR: Cannot insert a duplicatekey into a unique index Can't they use zero or null, and have the sequence value be computed? Is there some design decision we made to prevent this? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> test=> create table test (x int, y serial);
> CREATE
> test=> insert into test values (100, null);
> ERROR: ExecAppend: Fail to add null value in not null attribute y
gram.y thinks SERIAL is defined to mean NOT NULL:
| ColId SERIAL ColPrimaryKey { ColumnDef *n = makeNode(ColumnDef);
n->colname = $1; n->typename = makeNode(TypeName); n->typename->name =
xlateSqlType("integer"); n->raw_default = NULL; n->cooked_default = NULL;
=================> n->is_not_null = TRUE; n->is_sequence = TRUE; n->constraints =
$3;
$$ = (Node *)n; }
Offhand I don't see any fundamental reason why serial columns should
be restricted to be nonnull, but evidently someone did at some point.
regards, tom lane
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > test=> create table test (x int, y serial);
> > CREATE
> > test=> insert into test values (100, null);
> > ERROR: ExecAppend: Fail to add null value in not null attribute y
>
> gram.y thinks SERIAL is defined to mean NOT NULL:
>
> | ColId SERIAL ColPrimaryKey
> {
> ColumnDef *n = makeNode(ColumnDef);
> n->colname = $1;
> n->typename = makeNode(TypeName);
> n->typename->name = xlateSqlType("integer");
> n->raw_default = NULL;
> n->cooked_default = NULL;
> =================> n->is_not_null = TRUE;
> n->is_sequence = TRUE;
> n->constraints = $3;
>
> $$ = (Node *)n;
> }
>
> Offhand I don't see any fundamental reason why serial columns should
> be restricted to be nonnull, but evidently someone did at some point.
The actual null is not the issue. The issue is that if we have a
SERIAL column, and we try to put a NULL in there, shouldn't it put the
default sequence number in there?
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
On Fri, Oct 29, 1999 at 08:20:30PM -0400, Bruce Momjian wrote:
> >
> > Offhand I don't see any fundamental reason why serial columns should
> > be restricted to be nonnull, but evidently someone did at some point.
>
> The actual null is not the issue. The issue is that if we have a
> SERIAL column, and we try to put a NULL in there, shouldn't it put the
> default sequence number in there?
>
It seems logical that if a value was supplied for a serial column that
it would override the default. After all, SERIAL is just an int column
with a default based on a sequence, right?. If the default is always
used (even when a value is supplied) then that would be a REAL BIG problem.
Without making SERIAL a distinctly different datatype, I can't see how
a default sequence could behave differently for two tables created with
different syntax.
My 2 cents is that the current behavior is the correct behavior.
As far as the NULL goes, since the SERIAL column is assumed to be a
key and a unique index is created, having it NOT NULL seems like a
good idea. I don't know anyone who would have a key value be NULL,
and even if it could be NULL, you would olny be allowd one NULL.
--
The world's most ambitious and comprehensive PC game database project.
http://www.mobygames.com
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> Offhand I don't see any fundamental reason why serial columns should
>> be restricted to be nonnull, but evidently someone did at some point.
> The actual null is not the issue. The issue is that if we have a
> SERIAL column, and we try to put a NULL in there, shouldn't it put the
> default sequence number in there?
No, I wouldn't expect that at all. A default is inserted when you
don't supply anything at all for the column. Inserting an explicit
NULL means you want a NULL, and barring a NOT NULL constraint on
the column, that's what the system ought to insert. I can see no
possible justification for creating a type-specific exception to
that behavior.
If the original asker really wants to substitute something else for
an explicit null insertion, he could do it with a rule or a trigger.
But I don't think SERIAL ought to act that way all by itself.
regards, tom lane
>
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> Offhand I don't see any fundamental reason why serial columns should
> >> be restricted to be nonnull, but evidently someone did at some point.
>
> > The actual null is not the issue. The issue is that if we have a
> > SERIAL column, and we try to put a NULL in there, shouldn't it put the
> > default sequence number in there?
>
> No, I wouldn't expect that at all. A default is inserted when you
> don't supply anything at all for the column. Inserting an explicit
> NULL means you want a NULL, and barring a NOT NULL constraint on
> the column, that's what the system ought to insert. I can see no
> possible justification for creating a type-specific exception to
> that behavior.
>
> If the original asker really wants to substitute something else for
> an explicit null insertion, he could do it with a rule or a trigger.
> But I don't think SERIAL ought to act that way all by itself.
>
> regards, tom lane
I agree with tom.
If you don't want the user to be able to insert NULL, specify
NOT NULL explicitly. And if you want to force a default
behaviour, use a trigger (a rule can't do - sorry).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
> Bruce Momjian <maillist@candle.pha.pa.us> writes: > >> Offhand I don't see any fundamental reason why serial columns should > >> be restricted to be nonnull, but evidently someone did at some point. > > > The actual null is not the issue. The issue is that if we have a > > SERIAL column, and we try to put a NULL in there, shouldn't it put the > > default sequence number in there? > > No, I wouldn't expect that at all. A default is inserted when you > don't supply anything at all for the column. Inserting an explicit > NULL means you want a NULL, and barring a NOT NULL constraint on > the column, that's what the system ought to insert. I can see no > possible justification for creating a type-specific exception to > that behavior. > > If the original asker really wants to substitute something else for > an explicit null insertion, he could do it with a rule or a trigger. > But I don't think SERIAL ought to act that way all by itself. OK, I see now. In Informix, if you insert 0 into a serial column, you get the nextval assigned. However, I can see that is not logical. We have serial which defines a default for nextval(). Thanks. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > No, I wouldn't expect that at all. A default is inserted when you > > don't supply anything at all for the column. Inserting an explicit > > NULL means you want a NULL, and barring a NOT NULL constraint on > > the column, that's what the system ought to insert. I can see no > > possible justification for creating a type-specific exception to > > that behavior. > > > > If the original asker really wants to substitute something else for > > an explicit null insertion, he could do it with a rule or a trigger. > > But I don't think SERIAL ought to act that way all by itself. > > > > regards, tom lane > > I agree with tom. > > If you don't want the user to be able to insert NULL, specify > NOT NULL explicitly. And if you want to force a default > behaviour, use a trigger (a rule can't do - sorry). I thought Informix put the nextval with NULL, but I now see they do it with zero, which is pretty strange. Never mind. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026