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'
	CREATE
	test=> insert into test (x) values (100);
	INSERT 19359 1
	test=> 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 y
	test=> insert into test  values (100, 0);
	INSERT 19363 1
	test=> insert into test  values (100, 0);
	ERROR:  Cannot insert a duplicate key 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, Pennsylvania 19026

Fri, 29 Oct 1999 17:29:19 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Serial and NULL values
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

Fri, 29 Oct 1999 19:44:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Serial and NULL values 
> 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, Pennsylvania 19026

Fri, 29 Oct 1999 20:20:30 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Serial and NULL values
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

Fri, 29 Oct 1999 21:26:42 -0400
Brian Hirt <bhirt@mobygames.com>
Re: [HACKERS] Serial and NULL values
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

Fri, 29 Oct 1999 22:28:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Serial and NULL values 
>
> 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) #



Sat, 30 Oct 1999 15:36:55 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] Serial and NULL values
> 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, Pennsylvania 19026

Sun, 31 Oct 1999 08:15:12 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Serial and NULL values
> > 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, Pennsylvania 19026

Sun, 31 Oct 1999 08:17:32 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Serial and NULL values