Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] DROPping tables with SERIALs

From: Vadim Mikheev <vadim(at)krs(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] DROPping tables with SERIALs
Date: 1998-11-29 08:24:37
Message-ID: 366104C5.84A1CD8D@krs.ru (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 1998-11-29 17:07:11
Subject: Re: [HACKERS] DROPping tables with SERIALs
Previous:From: The Hermit HackerDate: 1998-11-29 05:59:39
Subject: Re: [HACKERS] configure revisions to autodetect USE_POSIX_SIGNALS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group