Re: [HACKERS] DROPping tables with SERIALs

Tom Lane wrote:
> 
> Vadim Mikheev <vadim@krs.ru> writes:
> > Also note that currently SERIAL doesn't work as
> > ppl expect -
> > 1. SERIAL should generate value if input value
> >    is NULL or 0;
> 
> No, I think it should *only* substitute for NULL.  Why assume
> zero is special?

As I remember this is how SERIAL works in Informix. 
Compatibility is good thing... but I have no objections.
Nevertheless, currently SERIAL doesn't work if input
value is NULL, only is not specified in INSERT:
DEFAULT is not appropriate for SERIAL in any case.

> 
> > 2. value generated should be max(this_field) + 1
> 
> That's not quite right.  If current max(serial_field) is 100, and
> I INSERT a tuple that gets serial 101, and then I DELETE that tuple,
> should the next insertion be given serial 101 again?  No.  You do need
> the separate sequence object as a record of the highest serial number
> ever assigned, regardless of whether that value is still present in the
> table.
> 
> What you really want is that if a non-null value is inserted into the
> serial field, and it is larger than the current readout of the
> associated sequence generator, then the sequence should be advanced to
> equal that inserted value.

Yes - this is what I meant...

> 
> Another question is whether a SERIAL field should automatically be
> UNIQUE (ie, create a unique index on it to prevent mistakes in manual
> insertion of values for the field).  I'm not sure that that should be
> forced to happen, but I think that most users would want the uniqueness
> constraint.  Maybe this just means a documentation change, with "SERIAL
> UNIQUE" being shown as the typical usage.

Once again - I would like to see SERIAL compatible with
SERIAL/IDENTY in other RDBMSes.

Vadim