Tom Lane wrote:
> "Rod Taylor" <rbt@zort.ca> writes:
>
>>>What happens is the sequence is shared between several tables (eg.
>>>invoice numbers or something)
>>
>
>>You cannot accomplish this situation by strictly using the SERIAL
>>type.
>
>
> But Chris is correct that there are borderline cases where we might
> do the wrong thing if we're not careful. The real question here,
> I suspect, is what rules pg_dump will use to decide that it ought
> to suppress a CREATE SEQUENCE command, DEFAULT clause, etc, in
> favor of emitting a SERIAL column datatype. In particular, ought it
> to depend on looking at the form of the name of the sequence?
> I can see arguments both ways on that...
>
I think that when SERIAL is used, the sequence should be tied
inextricably to the table which created it, and it should be hidden from
use for other purposes (perhaps similar to the way a toast table is). If
you *want* to use a sequence across several tables, then you don't use
SERIAL, you create a sequence.
Many people who come from an MS SQL Server background are used to an
IDENTITY column being tied transparently to the table in this fashion,
and they initially find sequences confusing. Conversely, people coming
from an Oracle background are quite comfortable with sequences, and
don't understand why it is necessary to have an IDENTITY type column at
all -- they seem too restrictive. We have people from both backgrounds
where I work, and both databases in use for various applications, and
this is at least what I have observed.
This is a chance for PostgreSQL to support people from both camps
equally well.
Anyway, just my 2c :-)
Joe