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

Re: [HACKERS] DROPping tables with SERIALs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vadim Mikheev <vadim(at)krs(dot)ru>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] DROPping tables with SERIALs
Date: 1998-11-28 20:30:34
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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?

> 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

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.

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.

			regards, tom lane


pgsql-hackers by date

Next:From: The Hermit HackerDate: 1998-11-28 21:14:32
Subject: Re: Mysql 321 - Mysql 322 - msql
Previous:From: John FieberDate: 1998-11-28 20:10:54
Subject: Re: Mysql 321 - Mysql 322 - msql

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