Re: SERIAL type feature request

Jan Wieck írta:

> On 12/3/2005 4:23 PM, Zoltan Boszormenyi wrote:
>
>> Hi!
>>
>> I would like to add an entry to PostgreSQL 8.2 TODO:
>> - Extend SERIAL to a full-featured auto-incrementer type.
>>
>> To achieve this, the following three requirements should be fulfilled:
>>
>> 1. The statement parser should be able to handle this:
>>
>> create table x (
>> id serial(N),
>> ...
>> );
>>
>> and behind the scenes this would translate into the "create sequence
>> ... start N"
>> before creating the table.
>
>
> Syntactic sugar with zero real value. A setval() after create table
> does exactly the same. Unless you extend your proposal to unambiguosly
> specify any or all of the serials properties (min, max, start, cache,
> cycle), this has to be rejected as incomplete.

I found this in the SQL2003 draft:

"
4.14.7 Identity columns

... An identity column has a start value, an increment, a maximum value,
a minimum value,
and a cycle option. ...
"

The exact properties of a sequence. It would be a good idea to be able
to provide
all these the same way PostgreSQL provides CREATE SEQUENCE.

>> 2. Upon INSERTing to a serial column, explicitly given 0 value or
>> 'default' keyword
>> or omitted field (implicit default) should be interchangeable.
>
>
>
> Why exactly would we treat an explicit zero different from any other
> explicit value? What you are asking for is to substitute an explicit,
> literal value presented by the user with something different. Sorry,
> but if Informix does THAT, then Informix is no better than MySQL.

Thinking about it more, 0 is a special value that a sequence created
with defaults
(just like the ones created for SERIAL fields) will not produce. If
PostgreSQL
provides a way to specify the sequence parameters for a SERIAL, there
may be other
values too, that a sequence created with given parameters will not produce.
At the extreme, they may be handled the same way. E.g.
CREATE SEQUENCE seq1 INCREMENT 2 MINVALUE 2 MAXVALUE 100;
won't produce 0, 1, any odd number between 3 and 99, and numbers 101 ...
2^64 -1.

>> 3. When a serial field value is given in an INSERT or UPDATE statement
>> and the value is larger the the current value of the sequence then
>> the sequence
>> should be modified accordingly.
>
>
>
> How about negative increment values, cycling sequences and max/minval?

For descending sequences, a lower value should update the sequence.

>> This is the way Informix handles its serial type, although it doesn't
>> seem
>> to have a visible sequence bound to the serial column.
>
>
>
> Have you considered asking Informix to do the reverse changes?

Hm. Good idea. I'll try. But I guess they won't backport it to 9.21. :-)

Best regards,
Zoltán Böszörményi