Re: [HACKERS] DROPping tables with SERIALs

Tom Lane wrote:
>
> Vadim Mikheev <vadim(at)krs(dot)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